Share via

Excel - countif when using auto filter

Anonymous
2013-09-11T06:47:54+00:00

I am a novice so apologise if this is obvious!

I have a sheet showing all the students in a particular academic year.  I want to filter by class so I can print a class list but at the bottom I want to know the number of male students in the class I have filtered for only not the number of males in the whole year.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2013-09-11T09:28:56+00:00

Let's say that the headers are in row 1 and the data in rows 2 through 100. And also that gender is in column B, stored as "Male" or "Female".

You can then use the following formula (borrowed from Aladin Akyurek) to count the filtered number of male students:

=SUMPRODUCT(SUBTOTAL(3, OFFSET(B2:B100, ROW(B2:B100)-MIN(ROW(B2:B100)), , 1)) * (B2:B100="Male"))

Was this answer helpful?

1 person found this answer helpful.
0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest