Excel has plenty of functions that can calculate mathematical operations, like SUM or AVERAGE, but when you use them in a range that is filtered or has hidden rows or columns they will still pick up the filtered or hidden values.
To dynamically calculate over a range and only calculate the visible values you can use the SUBTOTAL function.
Download File
If you would like to follow along download the attachment below.
The SUBTOTAL function
The SUBTOTAL function has two compulsory criteria:
=SUBTOTAL(function_num, ref1, [ref2], ...)
·       function_num – allows you to choose the type of calculation you want to perform
·       ref1 – the values you want to perform the calculation on
·       ref2 - (Optional) any additional values you want to perform the calculation on
Choosing the correct function_num
When you select the function_num parameter you are given a list of mathematical operators.
There are 22 options with the first half going from 1 to 11, and the second half going from 101 to 111.
Both will exclude filtered values, but the difference between these is whether or not you want to include hidden values, with 1 to 11 including hidden values in the operation and 101 to 111 excluding hidden values in the operation.
A full list of function_num and what they do can be found below:
Example Using SUM and SUBTOTAL
Unfiltered Range
In SUBTOTAL there are two operations which sum up values, 9 and 109.
Selecting 9 in SUBTOTAL will sum up all non-filtered values in a range, but will still include
hidden values in any sums.
Selecting 109 in SUBTOTAL will sum up all non-filtered values in ranges, but will also ignore values that are hidden.
The dataset below shows the population of each UK region.
Filtered Range
Hidden Rows
Example Applying Multiple refs Using SUM and SUBTOTAL
There are two compulsory criteria in SUBTOTAL, the function_num to choose the operation and ref1 to choose the values you want to perform the operation on.
The examples above only involved summing up one range so only ref1 was used.
There are optional criteria to add additional values to perform the operation on, this will show as ref2 and will be in square brackets which means it is optional. If you want to add more than two references you can do this by pressing the comma again.
Unfiltered Range with two refs
The dataset below contains sales for all UK regions for two quarters showing in columns B and C.
The SUM and SUBTOTAL functions have the sales in quarter 1 showing in ref1 and the sales for quarter 2 showing in ref2.
Filtered Range with two refs
Hidden Rows with two refs
Conclusion
The SUBTOTAL function can be used to calculate various mathematical operations, like SUM or AVERAGE, but can be set to exclude either filtered or hidden values.
To choose what to you want to exclude from your calculation you need to select a function_num, these are split into ranges with 1 to 9 excluding filtered values only, and 101 to 109 excluding hidden values as well.
Comments