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...


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=561009

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

One way, try:
=COUNTIF($B$6:$B$35,"0")-COUNTIF($B$6:$B$35,"=11")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mhz" wrote:

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...


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=561009

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

Cheers,
--
AP

"Max" a écrit dans le message de news:
...
countif(B6:B35,"0<11")

One way, try:
=COUNTIF($B$6:$B$35,"0")-COUNTIF($B$6:$B$35,"=11")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mhz" wrote:

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...


--
Mhz
------------------------------------------------------------------------
Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
View this thread:
http://www.excelforum.com/showthread...hreadid=561009

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

"Max" a écrit dans le message de news:
...
countif(B6:B35,"0<11")

One way, try:
=COUNTIF($B$6:$B$35,"0")-COUNTIF($B$6:$B$35,"=11")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mhz" wrote:

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...


--
Mhz
------------------------------------------------------------------------
Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
View this thread:
http://www.excelforum.com/showthread...hreadid=561009

My Goodness, what a wierd Formula... But YES! it definately 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! thanks..:)


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=561009

One way I know to accomplish this is by placing a Zero (0) in any unused
out of the way cell. Then copy and paste this formula in what ever cell
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.

Ed


--
patele
------------------------------------------------------------------------
patele's Profile: http://www.excelforum.com/member.php...o&userid=35849
View this thread: http://www.excelforum.com/showthread...hreadid=561009

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.

--
Regards

Roger Govier


"Max" wrote in message
...
countif(B6:B35,"0<11")

One way, try:
=COUNTIF($B$6:$B$35,"0")-COUNTIF($B$6:$B$35,"=11")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mhz" wrote:

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...


--
Mhz
------------------------------------------------------------------------
Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
View this thread:
http://www.excelforum.com/showthread...hreadid=561009

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)

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
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.

--
Regards

Roger Govier


"Max" wrote in message
...
countif(B6:B35,"0<11")

One way, try:
=COUNTIF($B$6:$B$35,"0")-COUNTIF($B$6:$B$35,"=11")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mhz" wrote:

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...


--
Mhz
------------------------------------------------------------------------
Mhz's Profile:
http://www.excelforum.com/member.php...o&userid=35980
View this thread:
http://www.excelforum.com/showthread...hreadid=561009

"Mhz" wrote:
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 ..)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

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

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


--
Mhz
------------------------------------------------------------------------
Mhz's Profile: http://www.excelforum.com/member.php...o&userid=35980
View this thread: http://www.excelforum.com/showthread...hreadid=561009

.. GOOD DEAL!

ay, that's what one always get around here <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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


"patele" wrote:


One way I know to accomplish this is by placing a Zero (0) in any unused
out of the way cell. Then copy and paste this formula in what ever cell
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.

Ed


--
patele
------------------------------------------------------------------------
patele's Profile: http://www.excelforum.com/member.php...o&userid=35849
View this thread: http://www.excelforum.com/showthread...hreadid=561009