Greater than and Less than in one formula

[BobTheRocker]

I am using the following formula in a worksheet

Please Login or Register  to view this content.

some cells in the range B6:B45 are 0 (zero) but I want the formula to ignore these. I have been trying to write a formula based on the above one that only counts cells less than 1 but greater than 0.00001.

Any ideas?

I'm really struggling. Any help greatfully received.

[Roger Govier]

Hi

You are using 2 different columns in your testing
Try
=SUMPRODUCT(--($A$6:$A$45<>""),--(A$6:A$45<1))

or
=SUMPRODUCT(--($A$6:$A$45>0),--(B$6:B$45<1))

[JBeaucaire]

So you really have three conditions:

=SUMPRODUCT(--($A$6:$A$45<>""), --(B$6:B$45<1), --(B$6:B$45>0))