# SUMPRODUCT/SUBTOTAL Problem

#### howsono

##### New Member
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

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

##### New Member
Hi Pete

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

thanks

Oli

#### XOR LX

##### Well-known Member
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

Replies
2
Views
41
Replies
9
Views
190
Replies
3
Views
113
Replies
12
Views
147
Replies
0
Views
35