blank (not empty) - treatment in countif statements using ""

[cbjohn1]

I have a formula that results in a number value or "" in "range"

using a countif formula the following two formulas produce internally inconsistent results. There are 27 total cells that are not empty in the range. 11 result in values and 16 result in ""

=countif(range,"") gives a result of 16
=countif(range,"<>"&"") gives a result of 27

What I'm trying to get is a formula that will count non-blank (""), non-empty cells

Is there anything that will work?

 

[dreid1011]

Have you tried =COUNTA(range) ?

 

[cbjohn1]

Sure have, but it doesn't work as it returns count for all cells that are not empty. it does not distinguish between cells with a formula in the (i.e. not empty) and cells that return "".

 

[dreid1011]

What I'm trying to get is a formula that will count non-blank (""), non-empty cells

Sure have, but it doesn't work as it returns count for all cells that are not empty. it does not distinguish between cells with a formula in the (i.e. not empty) and cells that return "".

I thought that was what you wanted.... and maybe we're thinking of different things here, but cells returning "" generally have a formula in them to return that blank. So, are you trying to distinguish between formulas returning "" and true blank cells with absolutely no value/formula in them?

 

[Marcelo Branco]

Maybe...

=SUMPRODUCT(--(range<>""))

M.

 

[Rick Rothstein]

Does this give you what you want...

=COUNTIF(range,"<>*")

 

[cbjohn1]

Not exactly. I want to count cells that return non-"" and non-empty.

E.g. - I have a formula that is iferror(1*somevalue,"") and copies down 30 cells. The formula results in a number value for 15 cells and, "" for 15 other cells and then are empty after that. I want a dynamic range of cells that return a number value.

Counta(range) doesn't work because it returns 30 i.e. any cell with a formula in it. Countif(range,"<>"&"") returns the same. Countif(range, "") returns both "" and empty cells.

Makes no sense.

 

[Marcelo Branco]

Have you tried the formulas in posts 5 or 6?

M.

 

[cbjohn1]

Does this give you what you want...

=COUNTIF(range,"<>*")

yes! Thanks!