counting in columns

[Mrs T.]

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.
Thanks

 

[Pete_UK]

Try this for your first query:

=SUMPRODUCT((A1:A100>=15)*(B1:B100>=15))

and this for your second:

=SUMPRODUCT((A1:A100>=15)*(B1:B100>=15)*(C1:C100>=15))

assuming scores for test A are in column A etc. Adjust the ranges to
suit your data.

Hope this helps.

Pete

 

[Mrs T.]

Hi Pete, I got #N/A in the cell when I did that. Is it because my test scores
come in from another page so have a formula behind the value?
Mrs T

 

[Pete_UK]

Well, the formula assumes the scores are numbers and not text values.

What formula do you use to bring the test scores across? If it gives
rise to any #N/A errors then you will get that as the result.

What columns are you using in your sheet?

Pete

 

[Mrs T.]

Columns that contain test scores are O, Y and AK rows 21:153. Formula that
brings data across is
=IF($Y21>0,LOOKUP($Y21,$AR$20:AR$62,AS$20:AS$62),""),same for other two
columns except $O21 and $AK21.
Thanks for your help.
Mrs T

 

[T. Valko]

=IF($Y21>0,LOOKUP($Y21,$AR$20:AR$62,AS$20:AS$62),"")

You didn't say if those formulas return any #N/A errors. Do they?

Post the exact formulas you tried that Pete suggested.

 

[Mrs T.]

Yes formula returns #N/A in empty cells, but it is a hidden column so I
ignored it!
Formulas I tried from Pete were
=SUMPRODUCT((Y21:Y153>=15)*(AK21:AK153>=15))
=SUMPRODUCT(O21:O153>=15)*(Y21:Y153>=15)*(AK21:AK153>=15)
which both gave #N/A in cell
Mrs T

 

[T. Valko]

Yes formula returns #N/A in empty cells

Ok, you should change those formulas so they don't return #N/A if you can.
Otherwise, you'll have to do something like this:

Array entered** :

=SUM(IF(ISNUMBER(Y21:Y153),Y21:Y153>=15)*IF(ISNUMBER(AK21:AK153),AK21:AK153>=15))

=SUM((IF(ISNUMBER(O21:O153),O21:O153>=15)*IF(ISNUMBER(Y21:Y153),Y21:Y153>=15)*IF(ISNUMBER(AK21:AK153),AK21:AK153>=15)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Mrs T.]

You are a star!!! Thank you so much - it works!!!
Mrs T

 

[T. Valko]

You're welcome!

See Mike's latest reply. It's much simpler than what I suggested.