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