promotion image of download ymail app
Promoted

Excel Sumproduct with subtotal Function?

Hi, I am trying to count only filtered data using the Sumproduct and Subtotal function. However, there are too many arguments? I am just trying to count all the rows with the year 2012. Thanks. Here is my Formula: 

=SUMPRODUCT((Table2910[Year]=2012)*(SUBTOTAL(103,OFFSET(A3,ROW(Table2910[Year]-MIN(ROW(Table2910[Year])),0))))

2 Answers

Relevance
  • 2 months ago

    One of the first things I was taught, many years ago, is that the open and closed parentheses ( ), must be balanced in an Excel formula.  Looking at your formula, it appears there are 8 @ open parentheses '(', but only 7 @ close parentheses ')'.  As you indicate you are getting a 'too many arguments' error, I suspect you need one more closing parenthesis in your formula to correct the argument structure possibly:

    =SUMPRODUCT((Table2910[Year]=2012)*(SUBTOTAL(103,OFFSET(A3,ROW(Table2910[Year]-MIN(ROW(Table2910[Year]))) ,0))))

    • Commenter avatarLogin to reply the answers
  • DEBS
    Lv 7
    2 months ago

    If all you want is those in 2012, then you do a sumif() or countif().

    Your question is unclear whether you mean 'filter' as in only counting or summing the rows that are visible of which some of those are 2012, or if you mean you want to 'filter' what numbers you are counting or summing.

    Also, consider a pivot table if you don't need this information directly on the datasheet.  It works great if you are going to be repeating this type of formula for multiple criteria.

    • Commenter avatarLogin to reply the answers
Still have questions? Get your answers by asking now.