ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   counting in columns (https://www.excelbanter.com/excel-worksheet-functions/229551-counting-columns.html)

Mrs T.
counting in columns
 
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
counting in columns
 
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

Mrs T.[_2_]
counting in columns
 
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

Pete_UK
counting in columns
 
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 -

Mrs T.[_2_]
counting in columns
 
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 -

T. Valko
counting in columns
 
=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 -

Mrs T.[_2_]
counting in columns
 
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:AK1 53=15)
which both gave #N/A in cell
Mrs T


"T. Valko" wrote:

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

T. Valko
counting in columns
 
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(ISNUMB ER(AK21:AK153),AK21:AK153=15))

=SUM((IF(ISNUMBER(O21:O153),O21:O153=15)*IF(ISNUM BER(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.


--
Biff
Microsoft Excel MVP


"Mrs T." wrote in message
...
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:AK1 53=15)
which both gave #N/A in cell
Mrs T


"T. Valko" wrote:

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

Mrs T.[_2_]
counting in columns
 
You are a star!!! Thank you so much - it works!!!
Mrs T :)

"T. Valko" wrote:

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(ISNUMB ER(AK21:AK153),AK21:AK153=15))

=SUM((IF(ISNUMBER(O21:O153),O21:O153=15)*IF(ISNUM BER(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.


--
Biff
Microsoft Excel MVP


"Mrs T." wrote in message
...
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:AK1 53=15)
which both gave #N/A in cell
Mrs T


"T. Valko" wrote:

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

T. Valko
counting in columns
 
You're welcome!

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

--
Biff
Microsoft Excel MVP


"Mrs T." wrote in message
...
You are a star!!! Thank you so much - it works!!!
Mrs T :)

"T. Valko" wrote:

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(ISNUMB ER(AK21:AK153),AK21:AK153=15))

=SUM((IF(ISNUMBER(O21:O153),O21:O153=15)*IF(ISNUM BER(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.


--
Biff
Microsoft Excel MVP


"Mrs T." wrote in message
...
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:AK1 53=15)
which both gave #N/A in cell
Mrs T


"T. Valko" wrote:

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


All times are GMT +1. The time now is 03:02 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com