I'm trying to count the number of rows that fall within a numerical range. For example I want to count the number of rows that contain a value >5 and <=10
I tried both count and countif and keep getting errors. Half if it is no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but when I put them together I have problems.
I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work either.
Can someone point me in the correct location?
Thanks!
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Thanks Bernie & Rowan!
I had found the answer a few minutes ago after searching the forums for the last hour and am using:
=COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND before and learned you can't with COUNTIF.
I also expanded on Rowan's second suggestion and am using:
=SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
in another area. I couldn't get it to work with the "--" part (and I'm not sure what it does) but when I removed it everything is working correctly.
Thanks Again
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
Cybertech,
=COUNTIF(E:E,">5") - COUNTIF(E:E,">10")
HTH,
Bernie
MS Excel MVP
"Cybertech" <[email protected]> wrote
in message news:[email protected]...
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile:
> http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
Two options:
=COUNTIF(E:E,"<=10")-COUNTIF(E:E,"<6")
or
=SUMPRODUCT(--(E1:E1000<=10),--(E1:E1000>5))
Note you can't use an entirecolumn reference in Sumproduct hence the E1000.
Regards
Rowan
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter)
SUM(IF((range>5)*(range<10),1,0))
Regards,
"Cybertech" wrote:
>
>
> numerical range. For example I want to count the number of rows that
> contain a value >5 and <=10
>
> I tried both count and countif and keep getting errors. Half if it is
> no problem, =COUNTIF(E:E,">5") works as does =COUNTIF(E:E,"<=10") but
> when I put them together I have problems.
>
> I book uses the example {=SUM((E:E>5)*(E:E<=10))} but that doesn't work
> either.
>
> Can someone point me in the correct location?
>
> Thanks!
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
You're welcome.
The -- (double unary minuses) work in much the same way as the * in your
formula. They each cause the sumproduct to resolve True and False answers
into 1's and 0's. It comes down do a matter of preference which you use so
your formula could be:
=SUMPRODUCT(--(B2:B1322=9),--(E2:E1322>5),--(E2:E1322<=10))
More info on sumproduct at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Regards
Rowan
"Cybertech" wrote:
>
> Thanks Bernie & Rowan!
>
> I had found the answer a few minutes ago after searching the forums for
> the last hour and am using:
>
> =COUNTIF(E:E,">5")-COUNTIF(E:E,">10") which works! I had been using AND
> before and learned you can't with COUNTIF.
>
> I also expanded on Rowan's second suggestion and am using:
>
> =SUMPRODUCT((B2:B1322=9)*(E2:E1322>5)*(E2:E1322<=10))
>
> in another area. I couldn't get it to work with the "--" part (and I'm
> not sure what it does) but when I removed it everything is working
> correctly.
>
> Thanks Again
>
>
> --
> Cybertech
> ------------------------------------------------------------------------
> Cybertech's Profile: http://www.excelforum.com/member.php...o&userid=26533
> View this thread: http://www.excelforum.com/showthread...hreadid=398428
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks