=IF($Y210,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.

--
Biff
Microsoft Excel MVP


"Mrs T." wrote in message
...
Columns that contain test scores are O, Y and AK rows 21:153. Formula that
brings data across is
=IF($Y210,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

"Pete_UK" wrote:

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

On May 2, 1:57 am, Mrs T. wrote:
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" wrote:
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

On May 2, 1:31 am, Mrs T. <Mrs wrote:
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- Hide quoted text -

- Show quoted text -