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


"pmdoherty" wrote in message
...
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