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)
"Fiona" wrote in message
...
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
"Alok" wrote:
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)<""))
"Fiona" wrote:
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
"Alok" wrote:
Try
=SUMPRODUCT(--(A1:A20<""))
"Fiona" wrote:
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
"Alan" wrote:
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.
"Fiona" wrote in message
...
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