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.
"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

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.