counting in columns

[Mrs T.]

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

 

[Mike H]

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:B25>15)*(C2:C25>15))

and for >15 in all 3 tests

=SUMPRODUCT((B2:B25>15)*(C2:C25>15)*(D225>15))

Change the ranges to suit.


Mike

 

[Mrs T.]

Hi Mike,
I still get #N/A. The formula that brings my data into columns is
=IF($Y30>0,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]

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:B25>15,IF(C2:C25>15,1)))

and for all three

=COUNT(IF(B2:B25>15,IF(C2:C25>15,IF(D225>15,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.]

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]

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.]

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]

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

Mike

 

[Mrs T.]

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]

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.]

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

 

[T. Valko]

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

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

 

[Mike H]

Hi,

Try this

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

Once again an array

Mike

 

[Mrs T.]

Perfect!!!
Thank you very much
Mrs T

Hi,

Try this

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

Once again an array

Mike

 

[Mrs T.]

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]

and saved myself 6 weeks of work!!

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

Thanks for the feedback!