Greater Than / Less Than Problem

[Mhz]

Don't want to sound silly here, but I am actually having a problem with
a formula that will allow me to count only values greater than 0 but
less than 11. Is this Possible in 1 unique formula? Heres the
situation:

Within Column B6:B35, I want a count of any number that is greater
than 0 but less than 11. I tried the following but it returns a 0:

countif(B6:B35,">0<11")

Thanks In Advance for any help here...

 

[Max]

countif(B6:B35,">0<11")

One way, try:
=COUNTIF($B$6:$B$35,">0")-COUNTIF($B$6:$B$35,">=11")

 

[Ardus Petus]

Another way:
=SUMPRODUCT((B6:B35>0)*(B6:B35<=11))

Cheers,

 

[Ardus Petus]

I meant <11, not <=11!
Sorry,
--
AP

One way, try:
=COUNTIF($B$6:$B$35,">0")-COUNTIF($B$6:$B$35,">=11")

 

[Mhz]

My Goodness, what a wierd Formula... But YES! it definately works!

Thanks Max, Much Appreciated! (I still dont understand the >= valu
on the last formula, but it actually works well. You would assume i
would contain a <= value) but who cares, IT WORKS! thanks..:

 

[patele]

One way I know to accomplish this is by placing a Zero (0) in any unuse
out of the way cell. Then copy and paste this formula in what ever cel
you want the total in.

=COUNTIF($B$6:$B$35,">Z1")+COUNTIF($B$6:$B$35,"<11")

Z1 is the cell I chose to put the (0) in. Hope this helps.

E

 

[Roger Govier]

Hi

As an alternative to Countif you could use the Sumproduct function
=SUMPRODUCT(--(INT(B6:B35/12)=0),B6:B35)

Taking the Integer of the numbers in your range divided by 12, will
return 0 for all values below 12, and 1 for any values greater than 11.
The first part of the test will therefore return True when less than or
equal to 11, and False when greater than 11.
The double unary minus -- coerces these True's to 1 and False's to 0.
Sumproduct then multiplies each of the values in your range by 1 or 0
and sums the results where all values outside the range 0 to 11 will
have been converted to 0.

 

[Roger Govier]

My apologies
Ardus' solution only appeared after posting mine (far easier logic) and
I noticed his correction to <11.
I too had misread your posting and assumed you wanted to include 11.
My formula would need to be modified to use INT(B6:B35/11)

 

[Max]

My Goodness, what a weird Formula... But YES! it definitely works!
Thanks Max, Much Appreciated! (I still dont understand the >= value
on the last formula, but it actually works well. You would assume it
would contain a <= value) but who cares, IT WORKS!

You're welcome.

We're simply slicing off the part we don't want (>=11)
from the returns by the 1st countif: COUNTIF($B$6:$B$35,">0")
via subtracting it with the 2nd one: COUNTIF($B$6:$B$35,">=11")
(visualize it as a number line ..)

 

[Mhz]

Thanks For All The Replies!! Roger, That is a Brain Digging Formul
But surely Works..

And Yes, Max Explained about the >=11 Factor, (Cutting of anthing Abov
11), WORKS GREAT!

Thanks to all of you.. More than one way to get a good result, fro
simple to complex...GOOD DEAL! ;

 

[Max]

.. GOOD DEAL!

ay, that's what one always get around here <g>

 

[JMB]

Actually, your formula will count all of the values in the range, with the
values between 0 and 11 counted twice.

">Z1" s/b ">"&Z1