counting in columns
 

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

counting in columns
 

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

counting in columns
 

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

counting in columns
 

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

counting in columns
 

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

counting in columns
 

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

counting in columns
 

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

counting in columns
 

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

counting in columns
 

I have a column on another sheet that tells me if a child has learning
problems. I have brought this data into my current sheet with the formula
=IF('M_FSP predict'!J8="","",'M_FSP predict'!J8). I want to count how many
children have learning problems but these can be entered into the sheet in a
variety of ways depending what the problem is.
Thanks
Mrs T

"Mike H" wrote:

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

counting in columns
 

Hi,

As you've already learned there are generally more that one way of doing
things in Excel. here's a method of counting text values in a range and once
again it's an array formula

=COUNT(IF(ISTEXT(A1:A25),1))

Mike

"Mrs T." wrote:

I have a column on another sheet that tells me if a child has learning
problems. I have brought this data into my current sheet with the formula
=IF('M_FSP predict'!J8="","",'M_FSP predict'!J8). I want to count how many
children have learning problems but these can be entered into the sheet in a
variety of ways depending what the problem is.
Thanks
Mrs T

"Mike H" wrote:

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

counting in columns
 

Hi Mike, I entered =COUNT(IF(ISTEXT(M21:M153),1)). There are 9 children
entered as having learning difficulties in the column but I get an answer of
132.
Mrs T

"Mike H" wrote:

Hi,

As you've already learned there are generally more that one way of doing
things in Excel. here's a method of counting text values in a range and once
again it's an array formula

=COUNT(IF(ISTEXT(A1:A25),1))

Mike

"Mrs T." wrote:

I have a column on another sheet that tells me if a child has learning
problems. I have brought this data into my current sheet with the formula
=IF('M_FSP predict'!J8="","",'M_FSP predict'!J8). I want to count how many
children have learning problems but these can be entered into the sheet in a
variety of ways depending what the problem is.
Thanks
Mrs T

"Mike H" wrote:

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

counting in columns
 

Try this version. It only counts TEXT and it *excludes* cells with formula
blanks:

=COUNTIF(M21:M153,"?*")

--
Biff
Microsoft Excel MVP


"Mrs T." wrote in message
...
Hi Mike, I entered =COUNT(IF(ISTEXT(M21:M153),1)). There are 9 children
entered as having learning difficulties in the column but I get an answer
of
132.
Mrs T

"Mike H" wrote:

Hi,

As you've already learned there are generally more that one way of doing
things in Excel. here's a method of counting text values in a range and
once
again it's an array formula

=COUNT(IF(ISTEXT(A1:A25),1))

Mike

"Mrs T." wrote:

I have a column on another sheet that tells me if a child has learning
problems. I have brought this data into my current sheet with the
formula
=IF('M_FSP predict'!J8="","",'M_FSP predict'!J8). I want to count how
many
children have learning problems but these can be entered into the sheet
in a
variety of ways depending what the problem is.
Thanks
Mrs T

"Mike H" wrote:

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

counting in columns
 

Hi,

Try this

=COUNT(IF(ISTEXT(M21:M153),IF(LEN(M21:M153)0,1)))

Once again an array

Mike

"Mrs T." wrote:

Hi Mike, I entered =COUNT(IF(ISTEXT(M21:M153),1)). There are 9 children
entered as having learning difficulties in the column but I get an answer of
132.
Mrs T

"Mike H" wrote:

Hi,

As you've already learned there are generally more that one way of doing
things in Excel. here's a method of counting text values in a range and once
again it's an array formula

=COUNT(IF(ISTEXT(A1:A25),1))

Mike

"Mrs T." wrote:

I have a column on another sheet that tells me if a child has learning
problems. I have brought this data into my current sheet with the formula
=IF('M_FSP predict'!J8="","",'M_FSP predict'!J8). I want to count how many
children have learning problems but these can be entered into the sheet in a
variety of ways depending what the problem is.
Thanks
Mrs T

"Mike H" wrote:

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

counting in columns
 

Perfect!!!
Thank you very much :)
Mrs T

"Mike H" wrote:

Hi,

Try this

=COUNT(IF(ISTEXT(M21:M153),IF(LEN(M21:M153)0,1)))

Once again an array

Mike

"Mrs T." wrote:

Hi Mike, I entered =COUNT(IF(ISTEXT(M21:M153),1)). There are 9 children
entered as having learning difficulties in the column but I get an answer of
132.
Mrs T

"Mike H" wrote:

Hi,

As you've already learned there are generally more that one way of doing
things in Excel. here's a method of counting text values in a range and once
again it's an array formula

=COUNT(IF(ISTEXT(A1:A25),1))

Mike

"Mrs T." wrote:

I have a column on another sheet that tells me if a child has learning
problems. I have brought this data into my current sheet with the formula
=IF('M_FSP predict'!J8="","",'M_FSP predict'!J8). I want to count how many
children have learning problems but these can be entered into the sheet in a
variety of ways depending what the problem is.
Thanks
Mrs T

"Mike H" wrote:

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

counting in columns
 

Hi,
thank you - this works as well, I should have just told you two what I
wanted the sheets to do in the beginning and saved myself 6 weeks of work!!
Mrs T :)

"T. Valko" wrote:

Try this version. It only counts TEXT and it *excludes* cells with formula
blanks:

=COUNTIF(M21:M153,"?*")

--
Biff
Microsoft Excel MVP


"Mrs T." wrote in message
...
Hi Mike, I entered =COUNT(IF(ISTEXT(M21:M153),1)). There are 9 children
entered as having learning difficulties in the column but I get an answer
of
132.
Mrs T

"Mike H" wrote:

Hi,

As you've already learned there are generally more that one way of doing
things in Excel. here's a method of counting text values in a range and
once
again it's an array formula

=COUNT(IF(ISTEXT(A1:A25),1))

Mike

"Mrs T." wrote:

I have a column on another sheet that tells me if a child has learning
problems. I have brought this data into my current sheet with the
formula
=IF('M_FSP predict'!J8="","",'M_FSP predict'!J8). I want to count how
many
children have learning problems but these can be entered into the sheet
in a
variety of ways depending what the problem is.
Thanks
Mrs T

"Mike H" wrote:

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

counting in columns
 

and saved myself 6 weeks of work!!

In some locations that's called job security. <grin

Thanks for the feedback!


--
Biff
Microsoft Excel MVP


"Mrs T." wrote in message
...
Hi,
thank you - this works as well, I should have just told you two what I
wanted the sheets to do in the beginning and saved myself 6 weeks of
work!!
Mrs T :)

"T. Valko" wrote:

Try this version. It only counts TEXT and it *excludes* cells with
formula
blanks:

=COUNTIF(M21:M153,"?*")

--
Biff
Microsoft Excel MVP


"Mrs T." wrote in message
...
Hi Mike, I entered =COUNT(IF(ISTEXT(M21:M153),1)). There are 9 children
entered as having learning difficulties in the column but I get an
answer
of
132.
Mrs T

"Mike H" wrote:

Hi,

As you've already learned there are generally more that one way of
doing
things in Excel. here's a method of counting text values in a range
and
once
again it's an array formula

=COUNT(IF(ISTEXT(A1:A25),1))

Mike

"Mrs T." wrote:

I have a column on another sheet that tells me if a child has
learning
problems. I have brought this data into my current sheet with the
formula
=IF('M_FSP predict'!J8="","",'M_FSP predict'!J8). I want to count
how
many
children have learning problems but these can be entered into the
sheet
in a
variety of ways depending what the problem is.
Thanks
Mrs T

"Mike H" wrote:

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