EXCEL - SUBTOTAL(function_num,ref)

Subtotal function can use to calculate the different totals such as Sum, Average and etc. For a long time, I could not understand why people bother to use subtotal function instead of corresponding function, such as Subtotal(1,ref)=average(ref). We have to remember or spend a minute to figure out which number we use.

Once, I build a report. The target audience wants to see the total value based on the criteria they filter the data. Then, I realized the power of SUBTOTAL Function.

For example, we want to calculate the total number of the items we bought and total values of the price we paid.

The SUBTOTAL() and COUNT() and SUM() functions gave us the same results.

However, if we filter the data and use the criteria (purchase price > $1,000), you can that the total values are different. SUBTOTAL() includes only the values which satisfy the criteria but COUNT() or SUM() include all the values.

© Copyright Exceltipsandkeys All Rights Reserved.