countif
 

When using the =countif function in my spreadsheet, the formula keeps adding
the blank spaces that are in my worksheet and I don't want the blank counted.
How do I solve this?

"JTee" wrote:

When using the =countif function in my spreadsheet, the formula keeps adding
the blank spaces that are in my worksheet and I don't want the blank counted.
How do I solve this?

I'm not sure of your criterion (second COUNTIF argument), so let's assume
you want to count the number of entries in A1:A20 which are not "RON", but
excluding blank or null string cell values in the count. Then you can use:

= COUNTIF(A1:A20,"<RON") - COUNTIF(A1:A20,"")

or the versatile SUMPRODUCT:

= SUMPRODUCT( (A1:A20<"RON") * (A1:A20 <""))

Ron - thanks for your thoughts. You are correct in assuming my criterion,
however, your formula is exactly what I am using, and the blank spaces are
counted as "not Ron". I haven't tried the =sumproduct. I am going to do
that and see how it works. Thanks again for your help.
"Ron Moore" wrote:


"JTee" wrote:

When using the =countif function in my spreadsheet, the formula keeps adding
the blank spaces that are in my worksheet and I don't want the blank counted.
How do I solve this?

I'm not sure of your criterion (second COUNTIF argument), so let's assume
you want to count the number of entries in A1:A20 which are not "RON", but
excluding blank or null string cell values in the count. Then you can use:

= COUNTIF(A1:A20,"<RON") - COUNTIF(A1:A20,"")

or the versatile SUMPRODUCT:

= SUMPRODUCT( (A1:A20<"RON") * (A1:A20 <""))

It may be that some of your cells aren't really blank, although they appear
to be so. That's the only possibility I can suggest. A cell may consist
entirely of space characters, for example. I've also read in some other site
(sorry, don't remember where) that when working with HTML, the non-breaking
space character (ASCII code 160) sometimes causes problems, although I've
never had to face this problem in my experience. You can check if cell A1,
for example, is really blank, or contains the null string value, by checking
its length with the formula =LEN(A1). To handle these possibilities, you can
use TRIM and/or SUBSTITUTE to convert such cells to null string values in
your listed data. I'm not sure how you're obtaining your list so that may
not be convenient. An alternative is to leave your listed data as is and
use the following formula:

=SUMPRODUCT((A1:A20<"RON")*(TRIM(SUBSTITUTE(A1:A2 0,CHAR(160),""))<""))

Note: You can also use this formula with SUM instead of SUMPRODUCT, but in
that case it must be entered as an array formula (entered with
CTRL-SHIFT-ENTER).

"JTee" wrote:


Ron - thanks for your thoughts. You are correct in assuming my criterion,
however, your formula is exactly what I am using, and the blank spaces are
counted as "not Ron". I haven't tried the =sumproduct. I am going to do
that and see how it works. Thanks again for your help.
"Ron Moore" wrote:


"JTee" wrote:

When using the =countif function in my spreadsheet, the formula keeps adding
the blank spaces that are in my worksheet and I don't want the blank counted.
How do I solve this?

I'm not sure of your criterion (second COUNTIF argument), so let's assume
you want to count the number of entries in A1:A20 which are not "RON", but
excluding blank or null string cell values in the count. Then you can use:

= COUNTIF(A1:A20,"<RON") - COUNTIF(A1:A20,"")

or the versatile SUMPRODUCT:

= SUMPRODUCT( (A1:A20<"RON") * (A1:A20 <""))