Counting items in a list

[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?

 

[Guest]

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]

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!

 

[T. Valko]

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

 

[Guest]

Try the following as an array formula:

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

 

[Guest]

The last two digits appear to have gone astray:

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

That is more like it.

 

[Guest]

=SUM(IF((A1:A5>1)*(A1:A5<10),1))

ctrl+shift+enter, not just enter