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
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" wrote in
message ...
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
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
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" wrote in message
...
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
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)" wrote in
message ...
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" wrote in
message ...
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)" wrote in
message ...
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" wrote in message
...
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
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" wrote in message
...
Try this:

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

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in message
...
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
wrote:
How can I count the number of items in a column whose length exceeds
zero?
--
jake

Thank you very much
--
jake


"Rick Rothstein (MVP - VB)" wrote:

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

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

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in
message ...
How can I count the number of items in a column whose length exceeds
zero?
--
jake

Thank you very much for taking the time to offer help. The formula seems to
ignore numbers.
--
jake


"T. Valko" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in message
...
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
wrote:
How can I count the number of items in a column whose length exceeds
zero?
--
jake

First, thank you for taking the time to respond. Your formula does count
text and numbers, but also counts nulls.
--
jake


"Pete_UK" wrote:

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
wrote:
How can I count the number of items in a column whose length exceeds zero?
--
jake

Biff's formula worked for me with either text or numbers. What formula are
you using in your cells (I'd like to see why you are saying it doesn't work
for you)?

Rick


"Jakobshavn Isbrae" wrote in
message ...
Thank you very much for taking the time to offer help. The formula seems
to
ignore numbers.
--
jake


"T. Valko" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in
message ...
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" wrote in message
...
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
wrote:
How can I count the number of items in a column whose length exceeds
zero?
--
jake

I'm guessing the answer will probably be "yes"

You are correct.

One of the biggest reasons and one that folks don't seem to consider is that
since SP works on arrays it evaluates *every* reference in the array
arguments.

=COUNTIF(A:A,"X")

Is very efficient because COUNTIF will only evaluate the used range.

=SUMPRODUCT(--(A1:A1000="x"))

Will evaluate every cell in A1:A1000 even if the used range only goes to
A100.

In Excel 2007:

=SUMPRODUCT(--(A:A="x"))

Is "crazy"!

I was more interested in its "scale of slowness" as opposed to the simple
fact that it is slower.

There is code here to test calculation times:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in message
...
Try this:

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

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in message ...
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" wrote in message
...
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
wrote:
How can I count the number of items in a column whose length exceeds
zero?
--
jake

Yes, that formula works on TEXT only.

In general, wildcards work on text only.

If you will have mixed data types then I would use Rick's suggestion:

=SUMPRODUCT(--(A1:A10<""))

--
Biff
Microsoft Excel MVP


"Jakobshavn Isbrae" wrote in
message ...
Thank you very much for taking the time to offer help. The formula seems
to
ignore numbers.
--
jake


"T. Valko" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in
message ...
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" wrote in message
...
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
wrote:
How can I count the number of items in a column whose length exceeds
zero?
--
jake

Ignore my previous post... I quite stupidly replaced the X with a 2 in the
data column's formula and left the rest of it alone when I tested Biff's
formula; however, I left the surrounding quote marks so the 2 was encased in
quotes when I tested Biff's formula (that is, the 2 was a text 2, not a
numerical 2, hence his formula appeared to work with numbers when in reality
it doesn't).

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
Biff's formula worked for me with either text or numbers. What formula are
you using in your cells (I'd like to see why you are saying it doesn't
work for you)?

Rick


"Jakobshavn Isbrae" wrote in
message ...
Thank you very much for taking the time to offer help. The formula seems
to
ignore numbers.
--
jake


"T. Valko" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in
message ...
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" wrote in message
...
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
wrote:
How can I count the number of items in a column whose length exceeds
zero?
--
jake

Thanks... I had forgotten to take in account the array aspect of SUMPRODUCT.

Rick


"T. Valko" wrote in message
...
I'm guessing the answer will probably be "yes"

You are correct.

One of the biggest reasons and one that folks don't seem to consider is
that since SP works on arrays it evaluates *every* reference in the array
arguments.

=COUNTIF(A:A,"X")

Is very efficient because COUNTIF will only evaluate the used range.

=SUMPRODUCT(--(A1:A1000="x"))

Will evaluate every cell in A1:A1000 even if the used range only goes to
A100.

In Excel 2007:

=SUMPRODUCT(--(A:A="x"))

Is "crazy"!

I was more interested in its "scale of slowness" as opposed to the simple
fact that it is slower.

There is code here to test calculation times:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
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" wrote in message
...
Try this:

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

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in message ...
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" wrote in message
...
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
wrote:
How can I count the number of items in a column whose length exceeds
zero?
--
jake

Thanks... as Biff pointed out, the slowness resides in the array aspect of
SUMPRODUCT.

Rick


"Pete_UK" wrote in message
...
Rick,

SUMIF and COUNTIF are much faster than SUMPRODUCT (or SUM(IF .. )
array formula) equivalents when you have a lot of rows. I analyse
telephone data for clients, where we might have 40,000 call records a
month (or more), and I have shown (to myself) that SP formulae are
much slower. It often pays to join several columns together and do a
SUMIF on the concatenated values rather than have SP with lots of
criteria in them (eg calls made of a particular type in a particular
charging period from a particular phone number between two particular
dates - you end up with 5 SP criteria each of 40,000 rows, whereas by
concatenating them together allows SUMIF to scan through just one
array).

Pete

On Jun 22, 6:14 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
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" wrote in message

...



Try this:

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

--
Biff
Microsoft Excel MVP

"Rick Rothstein (MVP - VB)" wrote
in
. ..
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" wrote in message
...
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
wrote:
How can I count the number of items in a column whose length exceeds
zero?
--
jake- Hide quoted text -

- Show quoted text -

I agree with your judgement on this. I was using an additional column with
len(a1) in the column. I was then using countif to count how many items in
this additional column were greater than zero.

Rick's single equation is a whole lot easier.

and by the way thanks to everyone who took the time to help me out on a
Sunday.
--
jake


"T. Valko" wrote:

Yes, that formula works on TEXT only.

In general, wildcards work on text only.

If you will have mixed data types then I would use Rick's suggestion:

=SUMPRODUCT(--(A1:A10<""))

--
Biff
Microsoft Excel MVP


"Jakobshavn Isbrae" wrote in
message ...
Thank you very much for taking the time to offer help. The formula seems
to
ignore numbers.
--
jake


"T. Valko" wrote:

Try this:

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

--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in
message ...
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" wrote in message
...
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
wrote:
How can I count the number of items in a column whose length exceeds
zero?
--
jake

Jakobshavn Isbrae
wrote...
How can I count the number of items in a column whose length exceeds zero?

Most efficient,

=ROWS(Range)*COLUMNS(Range)-COUNTBLANK(Range)

This will count every cell except truly blank cells (in the ISBLANK
sense) and cells evaluating to "". If you want to count only text
cells with 1 or more characters and cells containing numbers, try

=COUNT(Range)+COUNTIF(Range,"?*")