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

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

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

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 -

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 -

=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 -