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

Hi Fiona,

To count cells that contain a formula that returns a value and exclude those that return a blank, you can use the

Formula:

COUNTIF 


function in combination with the

Formula:

IF 


function.

Here are the steps:

1. Select the cell where you want to display the count result.
2. Type the following formula:
   Formula:

   =COUNTIF(range,"<")+COUNTBLANK(range)-COUNTIF(range," ") 

   
   - Replace "range" with the actual range of cells you want to count.
   - The "<" symbol means "not equal to", so the formula will count cells that are not blank or do not contain a space character.
   - The
   Formula:

   COUNTBLANK 

   function counts the number of blank cells in the range.
   - The "-
   Formula:

   COUNTIF(range," ") 

   " part subtracts the number of cells that contain a space character.
3. Press Enter to display the result.

This formula should give you the desired count result. Let me know if you have any questions or if there's anything else I can help you with.

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

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

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

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

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

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

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

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

On Jan 27, 1:35*pm, jimster wrote:
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.