Newbe Help with count/countif function
 

:confused: 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:E5)*(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" wrote
in message ...

:confused: 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:E5)*(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:E10005))

Note you can't use an entirecolumn reference in Sumproduct hence the E1000.

Regards
Rowan

"Cybertech" wrote:


:confused: 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:E5)*(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:E13225)*(E2:E1322<=1 0))

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((range5)*(range<10),1,0))

Regards,

"Cybertech" wrote:


:confused: 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:E5)*(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:E13225),--(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:E13225)*(E2:E1322<=1 0))

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