Counting items in a list
Ron's suggestion is the best solution for this specific situation.
But, if you want something similar to what you tried (array entered):
=SUM(IF(A1:A51,IF(A1:A5<100,1)))
The reason yours didn't work as expected is because the AND function
evaluates as a single condition. So, it would only return either a 1 or a 0.
If *every cell* in the range met the condition then the result would be 1.
If *any* cell in the range did not meet the condition then the result would
be 0.
Another way to do this (normally entered):
=SUMPRODUCT(--(A1:A51),--(A1:A5<100))
Biff
"Matt" wrote in message
ups.com...
That would work. However, I would like to use a formula syntax that is
similar to the original. I don't understand why that particular
formula is not working.
Thanks!
Ron Coderre wrote:
Try something like this:
With your posted example data in A1: A5
This formula returns the count of items between 1 and 10, exclusive
B1: =COUNTIF(A1:A5,"1")-COUNTIF(A1:A5,"=10")
Does that help?
***********
Regards,
Ron
XL2002, WinXP
"Matt" wrote:
Say if have a list of numbers in cells A1:A5
12
99
9
3
60
I would like to count the number of values that are between 1 and 10
exclusive using a formula something like this:
{=SUM(IF(AND(A1:A51,A1:A5<100),1,0))}
However I always get 0 for the answer. Any ideas?
|