Forum Discussion

ptedesco's avatar
ptedesco
Copper Contributor
May 02, 2018

Excel Filter and Sum Function

I have a question regarding MS Exel filters and sum function.

 

After a filter is applied to a sheet there is a list of rows that are displayed that meet the criteria of the filter. These rows have a column that is either blank or has a number value. I would like to add up the total value of all the filtered rows with number values in them. The way Excel works is it provides the total value of the rows PRIOR to applying the filter. Is there any way to accomplish this ?

 

Thank you

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    May 02, 2018

    Hi,

     

    Better to publish that question for Excel community here on TechCom.

     

    To sum you may use

    =SUBTOTAL(9,A:A)

    or 

    =AGGREGATE(9,7,A:A)

     

    • EricStarker's avatar
      EricStarker
      Icon for Community Manager rankCommunity Manager
      May 03, 2018

      I'm moving this question to the Excel community for better visibility.

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    May 03, 2018

    Sergei is dead on here.  Here's a great article on how SubTotal() works with hidden cells in many different scenarios.

     

    https://exceljet.net/excel-functions/excel-subtotal-function

     

    Here are the values to use in the first parameter Function_Num based on your scenario:

    Function Include hidden Ignore hidden
    AVERAGE 1 101
    COUNT 2 102
    COUNTA 3 103
    MAX 4 104
    MIN 5 105
    PRODUCT 6 106
    STDEV 7 107
    STDEVP 8 108
    SUM 9 109
    VAR 10 110
    VARP 11 111
    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      May 03, 2018

      With small comment, with filtered rows it doesn't matter what to use, 9 or 109

Resources