Counting Text Cells

F

Frank West

Hi, all.

I have a column with many different names. I can count all the cells
with the name, Bob for example: countif(a8:a1000,"Bob"), but what if I
want to get a running total of all the cells with names in them, but
not the blank cells. How do I do that?

Thanks,

Frank West
 
K

Kieran

=count(a8:a1000) will give count of all non-blank cells with text
values
=countblank(a8:a1000) will give count of all blank cells
=count(a8:a100) will count all the numbers in the range.
 
B

Bob Sullivan

Dear Frank,

Use the counta function. I think the other poster just
left off the "a"
example:
=counta(a8:a1000) will return all the non-blank cells in
a range.

Bob Sullivan
Springhouse
 
D

Dave Peterson

=count(a8:a1000) will count the number of numeric cells.
=counta(a8:a1000) will count the number of non-empty cells. This includes
formulas that evaluate to "" and look blank.

One way to count the non-blank text cells is:

=SUMPRODUCT(--(ISTEXT(A1:A1000)),--(LEN(A1:A1000)>0))
 
F

Frank West

Dear Frank,

Use the counta function. I think the other poster just
left off the "a"
example:
=counta(a8:a1000) will return all the non-blank cells in
a range.

Bob Sullivan
Click to expand...

Hi, Bob.

Here's the problem. If I have data in 18 cells and I use
counta(a8:a1000) it returns an answer of 982. I want the answer of
18. I have formulas in those cells and even though some of the cells
are blank I guess it still counts them.

Dave Peterson suggested this solution:

=SUMPRODUCT(--(ISTEXT(A1:A1000)),--(LEN(A1:A1000)>0))

And this works. I guess I was just wondering if the count, counta or
countif method would work as well.

Thanks to all.

FW
 
F

Frank West

One way to count the non-blank text cells is:

=SUMPRODUCT(--(ISTEXT(A1:A1000)),--(LEN(A1:A1000)>0))
Click to expand...

Hi, Dave. This formula works great, but I was wondering if you could
tell me what the dashes are for in the above formula? I have them in
some other formulas provided to me by posters here, but I can't find
anything in the help files to explain them.

Also, I understand the ISTEXT part of the formula, but why do you need
the LEN in there? Doesn't LEN count the number of characters in a
string? Since I'm only counting cells, how does that figure in?

Thanks,

FW
 
D

Dave Peterson

A single minus will convert True to -1. The second one will convert -1 to +1.

-False will be 0 and --false will still be 0.

And =sumproduct() expects numbers as its parameters.

Actually, =istext(a1:a1000) will return a 1000 element array of true/falses (as
will =len(a1:a1000)>0).

The -- part changes each of the elements to 0/1's.

Then the product part of sumproduct will multiply the corresponding elements.
The sum part sums the elements (but both corresponding elements have to be True
(or 1) to get anything besides 0.)
 
F

Frank West

A single minus will convert True to -1. The second one will convert -1 to +1.

-False will be 0 and --false will still be 0.

And =sumproduct() expects numbers as its parameters.

Actually, =istext(a1:a1000) will return a 1000 element array of true/falses (as
will =len(a1:a1000)>0).

The -- part changes each of the elements to 0/1's.

Then the product part of sumproduct will multiply the corresponding elements.
The sum part sums the elements (but both corresponding elements have to be True
(or 1) to get anything besides 0.)
Click to expand...

Thanks, Dave! That explains a lot.

FW
 
Top