Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Newbe Help with count/countif function
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 |
#2
|
|||
|
|||
Cybertech,
=COUNTIF(E:E,"5") - COUNTIF(E:E,"10") HTH, Bernie MS Excel MVP "Cybertech" wrote in message ... 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 |
#3
|
|||
|
|||
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: 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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
Hi,
You may try another solution. This is an array formula (Ctrl+Shift+Enter) SUM(IF((range5)*(range<10),1,0)) Regards, "Cybertech" wrote: 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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |