Richard asked in Computers & InternetSoftware · 2 months ago

# 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))))

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))))

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