Hi Mike,
I got a reply on the other thread that works - and so does yours though it's
different!! I'm pleased I didn't have to get rid of the #N/A in the other
columns as I wouldn't have a clue where to start!
Thanks very much for your help.
Mrs T :)

"Mike H" wrote:

Hi,

I just found your other thread and noticed the NA problem. the best way is
to eliminate the NA but you can leave them there and do it like this with an
array formula. See below on how to enter an array formula

For 15 in columns B & C

=COUNT(IF(B2:B2515,IF(C2:C2515,1)))

and for all three

=COUNT(IF(B2:B2515,IF(C2:C2515,IF(D2:D2515,1))) )


This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike


"Mrs T." wrote:

Please can anyone help - I am trying to set up a sheet for test scores. I
have a column for the name, a column for test A, a column for test B and a
column for test C results. How can I count how many people have scored over
15 in both tests A and B and how many people have scored over 15 in all three
tests? I am using Excel 2003. I got some help from Pete last night but didn't
get it sorted, I think because I am using a look up table (see thread below)
- 3:00 in the morning is maybe not the best time!
Thanks
Mrs T