SUMPRODUCT/SUBTOTAL Problem

[howsono]

I'm trying to put together a formula to count up how many students get each grade in a filtered list. I discovered the following formula somewhere which seemed to be working, but on the spreadsheet in question both D and D- grades are coming up as 2 whereas there are actually only 1 of each!

=SUMPRODUCT(SUBTOTAL(3,OFFSET($W$2:$W$162,ROW($W$2:$W$162)-MIN(ROW($W$2:$W$162)),,2)),--($W$2:$W$162=V181))

I can't pose attachments but can email or dropbox or whatever the file if there is not enough information here? Any help would be appreciated!!!

thanks

 

[NewOrderFac33]

Welcome to the Forum!

Don't know if it's of any help, but I had a similar issue with SUMPRODUCT last week where it was reporting inflated count values.

I never got to the bottom of it, but found that using SUMIFS instead resolved the issue and returned the correct values.

Pete

 

[howsono]

Hi Pete

SUMIFS I think won't work with filtered data though?

thanks

Oli

 

[XOR LX]

Hi.

Your reference and height parameters for OFFSET were not correct for your set-up.

Try:

=SUMPRODUCT(SUBTOTAL(3,OFFSET($W$2,ROW($W$2:$W$162)-MIN(ROW($W$2:$W$162)),,,)),--($W$2:$W$162=V181))

Regards