Counting items in a list

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

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:A5>1,A1:A5<100),1,0))}

However I always get 0 for the answer. Any ideas?
 
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
 
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'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:A5>1,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:A5>1),--(A1:A5<100))

Biff
 
The last two digits appear to have gone astray:

=SUM((A1:A5>1)*(A1:A5<100))

That is more like it.
 
Back
Top