SUMPRODUCT/SUBTOTAL Problem

howsono

New Member
Joined
Sep 25, 2014
Messages
2
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,252
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
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,032
Messages
5,526,353
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top