| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif numbers fall between two values
Hi,
Looking for a formula that will allow me to count how many numbers in a range fall between larger than 50% and smaller than 85% as well as larger than 85% and less than 100%. Currrently I am using a countif(range,<.5) and countif(range.5) but if I want to expand my criteria to count how many are 85 and 100, the values for 100 are in with the values from 85 and subtracting them gives me a negative number. Any ideas? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif numbers fall between two values
Try this
=COUNTIF(A1:A96,"50%")-COUNTIF(A1:A96,"85%") =COUNTIF(A1:A96,"85%")-COUNTIF(A1:A96,"100%") Mike Mike "Chris" wrote: Hi, Looking for a formula that will allow me to count how many numbers in a range fall between larger than 50% and smaller than 85% as well as larger than 85% and less than 100%. Currrently I am using a countif(range,<.5) and countif(range.5) but if I want to expand my criteria to count how many are 85 and 100, the values for 100 are in with the values from 85 and subtracting them gives me a negative number. Any ideas? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif numbers fall between two values
Chris,
Typically, for any band =countif(range,lowerlimit)-countif(range,upperlimit) OR =countif(range,<upperlimit)-countif(range,<lowerlimit) So, for your specific problem, for example: =COUNTIF(A:A,"85")-COUNTIF(A:A,"100") Of course, you may need to use = or <= in one or both of the comparisons, depending on how you want to handle border issues. HTH, Bernie MS Excel MVP "Chris" wrote in message ... Hi, Looking for a formula that will allow me to count how many numbers in a range fall between larger than 50% and smaller than 85% as well as larger than 85% and less than 100%. Currrently I am using a countif(range,<.5) and countif(range.5) but if I want to expand my criteria to count how many are 85 and 100, the values for 100 are in with the values from 85 and subtracting them gives me a negative number. Any ideas? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif numbers fall between two values
=SUMPRODUCT(--(A1:A100.5),--(A1:A100<.85))
=SUMPRODUCT(--(A1:A100.85),--(A1:A100<1)) Hope this helps. -- John C "Chris" wrote: Hi, Looking for a formula that will allow me to count how many numbers in a range fall between larger than 50% and smaller than 85% as well as larger than 85% and less than 100%. Currrently I am using a countif(range,<.5) and countif(range.5) but if I want to expand my criteria to count how many are 85 and 100, the values for 100 are in with the values from 85 and subtracting them gives me a negative number. Any ideas? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif numbers fall between two values
=SUM(COUNTIF(A:A,{"0.5","=0.85"})*{1,-1})
=SUM(COUNTIF(A:A,{"0.85","=1"})*{1,-1}) "Chris" wrote: Hi, Looking for a formula that will allow me to count how many numbers in a range fall between larger than 50% and smaller than 85% as well as larger than 85% and less than 100%. Currrently I am using a countif(range,<.5) and countif(range.5) but if I want to expand my criteria to count how many are 85 and 100, the values for 100 are in with the values from 85 and subtracting them gives me a negative number. Any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| syntax for countif when cells fall within a numerical range | Excel Worksheet Functions | |||
| Charting X values when they rise and fall | Charts and Charting in Excel | |||
| how do i sum 2 values that fall between date ranges? | Excel Worksheet Functions | |||
| making used values fall from a list | Excel Discussion (Misc queries) | |||
| Need totals of values that fall within a given year | Excel Worksheet Functions | |||
Hybrid Mode
