Excel Expert Tips - Summing Filtered Rows
In Excel you can sum a range of rows using the SUM command, however this will sum up all the rows even if they are filtered.
Excel Developer Tips Summing Filtered Rows
For example, the following tables show that the sum of the rows is always “21” even if we filter rows on some criteria.
Sum on unfiltered rows
Sum on filtered rows (where the letter is ‘A’)
Notice in the above that the Sum is still showing as 21, even though only “1” and “4” are showing. This may be fine if we always need to know the overall total, however it may be that we want to know the SUM for only the results we can see (in the above case this would be “5” (1 + 4).
We can get this result using the SUBTOTAL command.
To use the SUBTOTAL command we first of all specify the type of function you want to use (eg “Count”, “Sum”, “Average”, etc). These are selected using a number of which “9” is the number for “SUM”. You can then specify the range to use (in the above example this is range [B2:B7]) .
Therefore the full formula is “=SUBTOTAL(9,B2:B7)”
Initially the result looks the same as the SUM function
Both results say “21”, however it is when we filter that SUBTOTAL produces a different result.
In the above you can see that SUBTOTAL is only looking at the filtered results. However, if we do not use filter but instead choose to manually hide a row of data (as shown below), it does not work.
In the above you can see that both totals are showing as “21” even though we might have expected “3” for the SUBTOTAL.
Fortunately, SUBTOTAL has versions of the functions which can take this into account. You still use the SUBTOTAL command but use a different number to determine which function to use (these are the same as the first way but have “100” added to them). So in our case the formula is now “=SUBTOTAL(109,B2:B7)”
Using this formula we get a correct result as shown against “SUBTOTAL ‘109’ below)
Take a look at our demo on how to use the Subtotal Function in Excel
For a free consultation
Either call us now on 020 3817 6945 or fill out the form below and one of our team will be in touch.