Difficulty "countif"-ing number of "L1" or "L2" on an autofiltered

[pmdoherty]

I have a constantly updated master sheet of individual student's exam results
- in one column the exam levels, "L1" or "L2", in another "A" achieved, "Nr"
nearly achieved "NA" not achieved.
I then use "=master sheet:a1" etc to mirror all information to a number of
new sheets in the same document where the data is autofiltered by course
code, to show only results of each specified faculty.

What I have been attempting to do is to simply use =COUNTIF(J1:J692,"L1") to
get a total of the number of students who have sat Level 1. The problem is
that when i do this, it is also counting the "hidden" entries that are not
part of the filter.

Could anyone please advise on a method of counting ONLY the results shown
after i fun my auto-filter?

Thanks a lot,
Paul

 

[Bernie Deitrick]

Paul,

Don't use a formula - use a pivot table, and drop all your criteria fields onto either the row or
column area, and drop exam level onto the data field. Then, instead of using filters, select the
values that you want to see by checking/ unchecking them after clicking the dropdown arrow.

IF you do use a formula, use SUMPRODUCT, with your criteria included, like

=SUMPRODUCT((A1:A692="A")*(J1:J692="L1"))

HTH,
Bernie
MS Excel MVP

 

[Mike H]

Try this

=SUMPRODUCT(SUBTOTAL(3,OFFSET(J1:J692,ROW(J1:J692)-MIN(ROW(J1:J692)),,1))*(J1:J692="L1"))

Mike

 

[Ashish Mathur]

Hi,

If you have filtered on L1 and want to count the total number of visible
cells of the filtered range, then you can use the SUBTOTAL() function

=subtotal(3,J1:J692)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[pmdoherty]

Thanks Bernie - that's relly helpful and next time I will try it, but for now
Mike's formula has worked a treat. Thanks a lot - you've saved the day!

Thanks to everyone who has offered help here.

Regards,
Paul