• Excel Developer Tips Summing filtered rows

  • 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.

    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

    Summing filtered rows 1

     

    Sum on filtered rows (where the letter is ‘A’)

    Summing filtered rows 2

     

     

    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

     

    Summing filtered rows 3

     

    Both results say “21”, however it is when we filter that SUBTOTAL produces a different result.

     

    Summing filtered rows 4

     

    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.

     

    Summing filtered rows 5

     

    Summing filtered rows 6

     

    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)

     

    Summing filtered rows 7