|
|
|
|
|
|
|
|
|
|
| |
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" <Fiona@discussions.microsoft.com> wrote in message
> news:CA27D133-17D1-4B5D-93BF-C758F2EA0684@microsoft.com...
> >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
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
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" <Fiona@discussions.microsoft.com> wrote in message
> > news:CA27D133-17D1-4B5D-93BF-C758F2EA0684@microsoft.com...
> > >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
> >
> >
> > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <Fiona@discussions.microsoft.com> wrote in message
> > > news:CA27D133-17D1-4B5D-93BF-C758F2EA0684@microsoft.com...
> > > >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
> > >
> > >
> > > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
| |
 Answer |
|
| |
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" <Fiona@discussions.microsoft.com> wrote in message
> > > > news:CA27D133-17D1-4B5D-93BF-C758F2EA0684@microsoft.com...
> > > > >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
> > > >
> > > >
> > > > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <Fiona@discussions.microsoft.com> wrote in message
> > > > > news:CA27D133-17D1-4B5D-93BF-C758F2EA0684@microsoft.com...
> > > > > >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
> > > > >
> > > > >
> > > > > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <Fiona@discussions.microsoft.com> wrote in message
news:3273FA95-82D6-4850-B35C-FFE5FE97AA3D@microsoft.com...
> 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" <Fiona@discussions.microsoft.com> wrote in message
> > > > > > news:CA27D133-17D1-4B5D-93BF-C758F2EA0684@microsoft.com...
> > > > > > >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
> > > > > >
> > > > > >
> > > > > >
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
Question
Notify me of replies