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?

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?

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?

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?

Try the following as an array formula:

=SUM((A1:A51)*(A1:A5<1))
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Matt" wrote:

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?

The last two digits appear to have gone astray:

=SUM((A1:A51)*(A1:A5<100))

That is more like it.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Martin Fishlock" wrote:

Try the following as an array formula:

=SUM((A1:A51)*(A1:A5<1))
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Matt" wrote:

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?

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

ctrl+shift+enter, not just enter


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