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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
 
Try
=SUMPRODUCT(--(A1:A20<>""))

Fiona said:
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
Click to expand...
 
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
 
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)<>""))
 
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
 
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)
 
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.
 
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
 
Back
Top