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

Hi,

So, names in column A and test scores in columns B,C & D. We can ignore
column A because from you question you only want to know how many not who. So
for 15 in test A & B

=SUMPRODUCT((B2:B2515)*(C2:C2515))

and for 15 in all 3 tests

=SUMPRODUCT((B2:B2515)*(C2:C2515)*(D2:D2515))

Change the ranges to suit.


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

Hi Mike,
I still get #N/A. The formula that brings my data into columns is
=IF($Y300,LOOKUP($Y30,$AR$20:AR$62,AS$20:AS$62)," "). This gives #N/A in
empty cells - do I need to change that?
Thanks for your help
Mrs T

"Mike H" wrote:

Hi,

So, names in column A and test scores in columns B,C & D. We can ignore
column A because from you question you only want to know how many not who. So
for 15 in test A & B

=SUMPRODUCT((B2:B2515)*(C2:C2515))

and for 15 in all 3 tests

=SUMPRODUCT((B2:B2515)*(C2:C2515)*(D2:D2515))

Change the ranges to suit.


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

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

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

Your welcome and thanks for the feedback.

I just looked at the other solution, it's just 2 different approaches to the
same thing. In my formula I provide the value to sum (the 1 at the end) so
theres no need to check if each value is a number.

Mike

"Mrs T." wrote:

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

Hi Mike,
is it possible to use a similar formula to count cells that contain text
without specifying what the text is?
Mrs T

"Mike H" wrote:

Your welcome and thanks for the feedback.

I just looked at the other solution, it's just 2 different approaches to the
same thing. In my formula I provide the value to sum (the 1 at the end) so
theres no need to check if each value is a number.

Mike

"Mrs T." wrote:

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

Yes, counting non-specified text strings is dooable, give an example of what
your trying to do

Mike

"Mrs T." wrote:

Hi Mike,
is it possible to use a similar formula to count cells that contain text
without specifying what the text is?
Mrs T

"Mike H" wrote:

Your welcome and thanks for the feedback.

I just looked at the other solution, it's just 2 different approaches to the
same thing. In my formula I provide the value to sum (the 1 at the end) so
theres no need to check if each value is a number.

Mike

"Mrs T." wrote:

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