How to not count cells which contain a formula that returns " "?

[Guest]

I would like to count a column which contains formulas, some of them return
values and some of them return " "(blank). I need to count for those return
values and I tried to use counta but unsuccessful. Could someone pls help?
Thanks in advance!
Q from Fiona

 

[Alan]

You want to count all the cells in a range that are "" if I understand
correctly,
=COUNTIF(A1:A20,"")
Obviously adjust the range to suit your needs,
Regards,
Alan.

 

[Guest]

Thanks Alan. Actually, I tried countif as well. Btw, I want the result is to
count the non-blank cells rather than the blank cells and because the blank
cells actually contain formulas, so it doesn't allow me to ignore the blank
cells. I tried to use countif(A1:A20,<>" ") but unsuccessful too. Hope you
can find another solution for me, thanks!

Regards, Fiona

 

[Guest]

Try
=SUMPRODUCT(--(A1:A20<>""))

Thanks Alan. Actually, I tried countif as well. Btw, I want the result is to
count the non-blank cells rather than the blank cells and because the blank
cells actually contain formulas, so it doesn't allow me to ignore the blank
cells. I tried to use countif(A1:A20,<>" ") but unsuccessful too. Hope you
can find another solution for me, thanks!

Regards, Fiona

 

[Guest]

Dear Alok, the formula is not work as well. Here's what I want to do for your
ref.:

Firstly, assuming all cells contain formulas. Some of them will display as a
value and some of them will display as " ".

Column A
Row 1 May
Row 2
Row 3 Alice
Row 4
Row 5 Fiona

I want the result to be 3 but now is 5.

Kindly help!
Regards
Fiona

 

[Guest]

Fiona,
I did not realize that what you were saying is that some formulas return " "
(one space character. I though they return an empty string - that is "".

To take care of this all that you need to do is to change the formula to

=SUMPRODUCT(--(Trim(A1:A20)<>""))

 

[Guest]

Dear Alok

Thanks! I made it! As what you said, I'm wrongly put " "(one space
character) into my formulas. After I changed it to "", it works!

Btw, could you pls explain me the formula about =sumproduct (how to use it)
and the difference between with or without Trim?

Many thanks!
Fiona

 

[Bob Phillips]

The first formula

=SUMPRODUCT(--(A1:A20<>""))

is overkill, COUNTIF will do it

=COUNTIF(A1:A20,"<>")

but you do need it if you need to TRIM the cell values.The TRIM strips off
leadinmg and trailing spaces, and so a cell that has no characters, and a
cell with all space characters are both counted as empty.

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[jimster]

Bob Philips is wrong, the COUNTIF() function counts the matching text in the
cell range including those in formulas.
I had a column with the same formula in each cell. The cell returned blank
or "*". The countif() function gave 214 which was the number of cells
containing the formula! - there were only 46 asterisks displayed from the
formulas.

 

[Pete_UK]

Asterisks are used as wildcard characters, so you were probably
counting everything if you used asterisks in your COUNTIF formula. Use
the tilde symbol ~ before the asterisk to tell Excel to treat it as a
normal character.

Hope this helps.

Pete