J
Jakobshavn Isbrae
How can I count the number of items in a column whose length exceeds zero?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Gord Dibben
=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
Any non-blank cell would have a length greater than 0
Gord Dibben MS Excel MVP
R
Rick Rothstein \(MVP - VB\)
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
=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
P
Pete_UK
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
=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
P
pdberger
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
that are in a column?" If so, then:
=COUNTIF(A:A,">0")
HTH
P
Pete_UK
Might be text values he wants to count.
Pete
Pete
R
Rick Rothstein \(MVP - VB\)
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
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
=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
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
R
Rick Rothstein \(MVP - VB\)
Or, I guess more simply (that is, one less function call), this...
=SUMPRODUCT(--(A1:A1000<>""))
Rick
=SUMPRODUCT(--(A1:A1000<>""))
Rick
T
T. Valko
Try this:
=COUNTIF(A1:A10,"?*")
=COUNTIF(A1:A10,"?*")
R
Rick Rothstein \(MVP - VB\)
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
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
J
Jakobshavn Isbrae
Thank you very much
--
jake
--
jake
Rick Rothstein (MVP - VB) said:Or, I guess more simply (that is, one less function call), this...
=SUMPRODUCT(--(A1:A1000<>""))
Rick
J
Jakobshavn Isbrae
Thank you very much for taking the time to offer help. The formula seems to
ignore numbers.
ignore numbers.
J
Jakobshavn Isbrae
First, thank you for taking the time to respond. Your formula does count
text and numbers, but also counts nulls.
text and numbers, but also counts nulls.
R
Rick Rothstein \(MVP - VB\)
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
you using in your cells (I'd like to see why you are saying it doesn't work
for you)?
Rick
Jakobshavn Isbrae said:Thank you very much for taking the time to offer help. The formula seems
to
ignore numbers.
T
T. Valko
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"!
There is code here to test calculation times:
http://msdn2.microsoft.com/en-us/library/aa730921.aspx
--
Biff
Microsoft Excel MVP
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) said: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
T. Valko
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
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 said:Thank you very much for taking the time to offer help. The formula seems
to
ignore numbers.
P
Pete_UK
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
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
R
Rick Rothstein \(MVP - VB\)
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
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
R
Rick Rothstein \(MVP - VB\)
Thanks... I had forgotten to take in account the array aspect of SUMPRODUCT.
Rick
Rick
R
Rick Rothstein \(MVP - VB\)
Thanks... as Biff pointed out, the slowness resides in the array aspect of
SUMPRODUCT.
Rick
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
SUMPRODUCT.
Rick
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
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.