How can I count the number of items in a column whose length exceeds zero?
--
jake

=COUNTA(A:A) will count non-blank cells in column A

Any non-blank cell would have a length greater than 0


Gord Dibben MS Excel MVP


On Sun, 22 Jun 2008 08:16:01 -0700, Jakobshavn Isbrae
<(E-Mail Removed)> wrote:

>How can I count the number of items in a column whose length exceeds zero?

You could try this...

=SUMPRODUCT(--(LEN(A1:A1000)>0))

where you would chose a top-end to the range greater than the largest row
you ever expect to use.

Rick


"Jakobshavn Isbrae" <(E-Mail Removed)> wrote in
message news:4B2FCB59-8283-4765-859D-(E-Mail Removed)...
> How can I count the number of items in a column whose length exceeds zero?
> --
> jake

Try using COUNTIF, i.e.:

=COUNTIF(A:A,"<>")

will count everything in column A which is not blank (or appears to be
blank, like a formula returning "")

Hope this helps.

Pete

On Jun 22, 4:16*pm, Jakobshavn Isbrae
<JakobshavnIsb...@discussions.microsoft.com> wrote:
> How can I count the number of items in a column whose length exceeds zero?
> --
> jake

Do you mean "How can I count the number of items whose length exceeds zero
that are in a column?" If so, then:

=COUNTIF(A:A,">0")

HTH

"Jakobshavn Isbrae" wrote:

> How can I count the number of items in a column whose length exceeds zero?
> --
> jake

Might be text values he wants to count.

Pete

On Jun 22, 4:49*pm, pdberger <pdber...@discussions.microsoft.com>
wrote:
> Do you mean "How can I count the number of items whose length exceeds zero
> that are in a column?" *If so, then:
>
> =COUNTIF(A:A,">0")
>
> HTH

I put this formula in A1...

=IF(MOD(ROW(A1),3),"X","")

and copied it down to A10. On my copy of XL2003, your formula, as well as
the other formulas that were posted (except for mine, of course<g>), returns
10 instead of 7 meaning it counted the blank (looking) cells as well as the
non-blank looking ones.

Rick


"Pete_UK" <(E-Mail Removed)> wrote in message
news:bc29cc48-17f2-4c1c-8c7f-(E-Mail Removed)...
Try using COUNTIF, i.e.:

=COUNTIF(A:A,"<>")

will count everything in column A which is not blank (or appears to be
blank, like a formula returning "")

Hope this helps.

Pete

On Jun 22, 4:16 pm, Jakobshavn Isbrae
<JakobshavnIsb...@discussions.microsoft.com> wrote:
> How can I count the number of items in a column whose length exceeds zero?
> --
> jake

Or, I guess more simply (that is, one less function call), this...

=SUMPRODUCT(--(A1:A1000<>""))

Rick


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:%23$(E-Mail Removed)...
> You could try this...
>
> =SUMPRODUCT(--(LEN(A1:A1000)>0))
>
> where you would chose a top-end to the range greater than the largest row
> you ever expect to use.
>
> Rick
>
>
> "Jakobshavn Isbrae" <(E-Mail Removed)> wrote in
> message news:4B2FCB59-8283-4765-859D-(E-Mail Removed)...
>> How can I count the number of items in a column whose length exceeds
>> zero?
>> --
>> jake
>

Try this:

=COUNTIF(A1:A10,"?*")

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>I put this formula in A1...
>
> =IF(MOD(ROW(A1),3),"X","")
>
> and copied it down to A10. On my copy of XL2003, your formula, as well as
> the other formulas that were posted (except for mine, of course<g>),
> returns 10 instead of 7 meaning it counted the blank (looking) cells as
> well as the non-blank looking ones.
>
> Rick
>
>
> "Pete_UK" <(E-Mail Removed)> wrote in message
> news:bc29cc48-17f2-4c1c-8c7f-(E-Mail Removed)...
> Try using COUNTIF, i.e.:
>
> =COUNTIF(A:A,"<>")
>
> will count everything in column A which is not blank (or appears to be
> blank, like a formula returning "")
>
> Hope this helps.
>
> Pete
>
> On Jun 22, 4:16 pm, Jakobshavn Isbrae
> <JakobshavnIsb...@discussions.microsoft.com> wrote:
>> How can I count the number of items in a column whose length exceeds
>> zero?
>> --
>> jake
>

Yes, of course, wildcards... that works fine.

I find my inclination is to go to the SUMPRODUCT function because of it vast
flexibility as a calculation engine. I was wondering if you knew (or if
anyone else reading this message knows) whether the SUMPRODUCT is, by its
very nature as a universal type calculation engine, a necessarily slow
function compared to the more targeted functions (such as your COUNTIF
formula compared to my SUMPRODUCT formula)? I'm guessing the answer will
probably be "yes", but I was more interested in its "scale of slowness" as
opposed to the simple fact that it is slower.

Rick


"T. Valko" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Try this:
>
> =COUNTIF(A1:A10,"?*")
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
>>I put this formula in A1...
>>
>> =IF(MOD(ROW(A1),3),"X","")
>>
>> and copied it down to A10. On my copy of XL2003, your formula, as well as
>> the other formulas that were posted (except for mine, of course<g>),
>> returns 10 instead of 7 meaning it counted the blank (looking) cells as
>> well as the non-blank looking ones.
>>
>> Rick
>>
>>
>> "Pete_UK" <(E-Mail Removed)> wrote in message
>> news:bc29cc48-17f2-4c1c-8c7f-(E-Mail Removed)...
>> Try using COUNTIF, i.e.:
>>
>> =COUNTIF(A:A,"<>")
>>
>> will count everything in column A which is not blank (or appears to be
>> blank, like a formula returning "")
>>
>> Hope this helps.
>>
>> Pete
>>
>> On Jun 22, 4:16 pm, Jakobshavn Isbrae
>> <JakobshavnIsb...@discussions.microsoft.com> wrote:
>>> How can I count the number of items in a column whose length exceeds
>>> zero?
>>> --
>>> jake
>>
>
>