CountA formula counting blank cells with formula

[vikramisrani]

Hi,

I am using CountA to count the cells yielding a value.

These cell have a formula that leaves the cell "blank" if a criteria is not met.

However, ContA is also counting the cells with no value, but just a formula (yielding blank as result).

Any suggestion on this issue will be a great help.

Please suggest.

 

[Andrew Poulsom]

Welcome to MrExcel.

A cell that contains "" is not blank. What's in the cells - text or numbers?

 

[Arithos]

Hi,

I am using CountA to count the cells yielding a value.

These cell have a formula that leaves the cell "blank" if a criteria is not met.

However, ContA is also counting the cells with no value, but just a formula (yielding blank as result).

Any suggestion on this issue will be a great help.

Please suggest.

Hmm, you just want the cells with values(numbers) use =COUNT()
COUNTA means countALL. So you will count cells with formulas aswell, even though they return ""

Does this help`?

 

[MrMeinHerr]

Hi,

I am using CountA to count the cells yielding a value.

These cell have a formula that leaves the cell "blank" if a criteria is not met.

However, ContA is also counting the cells with no value, but just a formula (yielding blank as result).

Any suggestion on this issue will be a great help.

Please suggest.

COUNTA counts non-blank cells, and "" is not considered blank, so a cell containing "" will be counted. If you are trying to count numbers, simply use the COUNT function. If you want to count all non-blank values but exclude the "" values, you could try an array formula. The following will achieve what I expect you are desiring for column A:

=SUM(IF(LEN(A:A)>0,1,0))

Remember that the array formula must be entered with Ctrl+Shift+Enter and not just Enter.

I hope this helps!

 

[Arithos]

COUNTA counts non-blank cells, and "" is not considered blank, so a cell containing "" will be counted. If you are trying to count numbers, simply use the COUNT function. If you want to count all non-blank values but exclude the "" values, you could try an array formula. The following will achieve what I expect you are desiring for column A:

=SUM(IF(LEN(A:A)>0,1,0))

Remember that the array formula must be entered with Ctrl+Shift+Enter and not just Enter.

I hope this helps!

You could avoid an Array formula by using this:

=SUMPRODUCT(--(A:A<>"")) This counts text and numbers, but not the cells with formulas returning ""

 

[MrMeinHerr]

Another solution that avoids the array formula is:

=COUNT(A:A)+COUNTIF(A:A,"?*")

There are often many ways to skin a cat!

 

[vikramisrani]

Text

 

[vikramisrani]

Thanks Arithos, but I have text in cells and not numbers. Is there any alternative for that?

 

[vikramisrani]

This one worked for me. Thanks Arithos This forum is awsome!!

 

[Arithos]

Thanks Arithos, but I have text in cells and not numbers. Is there any alternative for that?

=SUMPRODUCT(--(A:A<>"")) this returns text aswell as numbers,

is there numbers inbetween the text you dont want to count?

If not use the formula I posted here. (and earlier in the thread) =)


**EDIT** Just saw your reply! Glad to help, and yes, it is awesome ;D