|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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?action=getinfo&userid=35980
View this thread: http://www.excelforum.com/showthread.php?threadid=561009
|
|
|
[Quoted Text] > 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?action=getinfo&userid=35980
> View this thread: http://www.excelforum.com/showthread.php?threadid=561009
>
>
|
|
Another way:
=SUMPRODUCT((B6:B35>0)*(B6:B35<=11))
Cheers,
--
AP
"Max" <demechanik[ at ]yahoo.com> a écrit dans le message de news:
66DDBE74-072F-46B3-8187-069E744AE1C2[ at ]microsoft.com...
[Quoted Text] >> 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?action=getinfo&userid=35980>> View this thread: >> http://www.excelforum.com/showthread.php?threadid=561009>> >>
|
|
I meant <11, not <=11!
Sorry,
--
AP
"Max" <demechanik[ at ]yahoo.com> a écrit dans le message de news:
66DDBE74-072F-46B3-8187-069E744AE1C2[ at ]microsoft.com...
[Quoted Text] >> 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?action=getinfo&userid=35980>> View this thread: >> http://www.excelforum.com/showthread.php?threadid=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" <demechanik[ at ]yahoo.com> wrote in message
news:66DDBE74-072F-46B3-8187-069E744AE1C2[ at ]microsoft.com...
[Quoted Text] >> 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?action=getinfo&userid=35980>> View this thread: >> http://www.excelforum.com/showthread.php?threadid=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" <roger[ at ]technologyNOSPAM4u.co.uk> wrote in message
news:%23D8yrTmpGHA.2460[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > 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" <demechanik[ at ]yahoo.com> wrote in message > news:66DDBE74-072F-46B3-8187-069E744AE1C2[ at ]microsoft.com... >>> 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?action=getinfo&userid=35980>>> View this thread: >>> http://www.excelforum.com/showthread.php?threadid=561009>>> >>> > >
|
|
"Mhz" wrote:
[Quoted Text] > 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
---
|
|
|
|
|
|
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:
[Quoted Text]
|
|
|