Counting non-blank cells
 

I have a column with cells that contain the results of a formula:
=IF(B8=B9,"",B9)
In some cases the value is blank (""), in others the value is filled. When I
try to count the values using COUNTA, the resulting count includes the cells
where the formula result is "". How do I count only the cells that have a
value other than ""?

Thanks,
Joe M.

Counting non-blank cells
 

One way

=SUMPRODUCT(--(LEN(A1:A10)0))


--
Regards,

Peo Sjoblom


"Joe M." wrote in message
...
I have a column with cells that contain the results of a formula:
=IF(B8=B9,"",B9)
In some cases the value is blank (""), in others the value is filled. When
I
try to count the values using COUNTA, the resulting count includes the
cells
where the formula result is "". How do I count only the cells that have a
value other than ""?

Thanks,
Joe M.

Counting non-blank cells
 

Peo Sjoblom" wrote in message
... One way

=SUMPRODUCT(--(LEN(A1:A10)0))

Doesn't that return 0 for cells with "" in them?

I was going to post:

=COUNTBLANK(A1:A10)-COUNTIF(A1:A10,"*")

or

=COUNTIF(A1:A10,"=")

or the array entered:

=SUM(--ISBLANK(A1:A10))

But I was trying to work out if Harlan would come along and point out where
they would fail as well.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

Counting non-blank cells
 

I have a column with cells that contain the results of a formula:
=IF(B8=B9,"",B9)
In some cases the value is blank (""), in others the value is filled.
When I try to count the values using COUNTA, the resulting count
includes the cells where the formula result is "". How do I count only
the cells that have a value other than ""?

Maybe COUNTBLANK() will help. It counts empty cells, including those with a
formula that returns empty text.

Subtracting COUNTBLANK() from the total number of cells would count the
cells that have a value other than "".

Counting non-blank cells
 

I assumed that's what the OP wanted. I read his question as to count cells
that are not blank,
whether it is from the cell being empty or from it having a formula
returning a blank

Peo


"Sandy Mann" wrote in message
...
Peo Sjoblom" wrote in message
... One way

=SUMPRODUCT(--(LEN(A1:A10)0))

Doesn't that return 0 for cells with "" in them?

I was going to post:

=COUNTBLANK(A1:A10)-COUNTIF(A1:A10,"*")

or

=COUNTIF(A1:A10,"=")

or the array entered:

=SUM(--ISBLANK(A1:A10))

But I was trying to work out if Harlan would come along and point out
where they would fail as well.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

Counting non-blank cells
 

Yes, on re-reading the OP's post you are quite right - I got it wrong! :-(

My appologies.

--


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Peo Sjoblom" wrote in message
...
I assumed that's what the OP wanted. I read his question as to count cells
that are not blank,
whether it is from the cell being empty or from it having a formula
returning a blank

Peo


"Sandy Mann" wrote in message
...
Peo Sjoblom" wrote in message
... One way

=SUMPRODUCT(--(LEN(A1:A10)0))

Doesn't that return 0 for cells with "" in them?

I was going to post:

=COUNTBLANK(A1:A10)-COUNTIF(A1:A10,"*")

or

=COUNTIF(A1:A10,"=")

or the array entered:

=SUM(--ISBLANK(A1:A10))

But I was trying to work out if Harlan would come along and point out
where they would fail as well.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

Counting non-blank cells
 

"Sandy Mann" wrote...
Peo Sjoblom" wrote in message
....
=SUMPRODUCT(--(LEN(A1:A10)0))

Why bother using LEN?

=SUMPRODUCT(--(A1:A10<""))

Doesn't that return 0 for cells with "" in them?

Yup. All about specifyine what's intended by 'non-blank', which could be as
above, or

=SUMPRODUCT(--(TRIM(A1:A10)<""))

or

=SUMPRODUCT(--(TRIM(SUBSTITUTE(A1:A10,CHAR(160),""))<""))

I was going to post:

=COUNTBLANK(A1:A10)-COUNTIF(A1:A10,"*")

This can produce negative numbers.

or

=COUNTIF(A1:A10,"=")

or the array entered:

=SUM(--ISBLANK(A1:A10))
....

These count truly blank cells. OP wanted to count nonblank cells. OP should
use Peo's formula, or one of my adaptations of it. There's even

=COUNT(A1:A10)+COUNTIF(A1:A10,"?*")

but this doesn't include error or boolean values.

Counting non-blank cells
 

Joe M.

your question is quite tricky...

=IF(B8=B9,"",B9)

if your data range along column B (eg) B8:B18
maybe u can modify the quick count somekinda this way...

=SUMPRODUCT(--(LEN(B9:B18)0))

*exclude the first cell on the range which is B* in this example...
<g.
--
regards,
driller

*****
- dive with Jonathan Seagull



"Joe M." wrote:

I have a column with cells that contain the results of a formula:
=IF(B8=B9,"",B9)
In some cases the value is blank (""), in others the value is filled. When I
try to count the values using COUNTA, the resulting count includes the cells
where the formula result is "". How do I count only the cells that have a
value other than ""?

Thanks,
Joe M.