| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif & Sumif
how do i doa formula that will count or sum a bunch of numbers that are
between a particular range. i.e. 100 but < 200 ? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif & Sumif
For count:
=SUM(COUNTIF(A:A,{"100","=200"})*{1,-1}) For sum: =SUM(SUMIF(A:A,{"100","=200"})*{1,-1}) "JulesHR" wrote: how do i doa formula that will count or sum a bunch of numbers that are between a particular range. i.e. 100 but < 200 ? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif & Sumif
Jules,
=SUMIF(Rangewithnumbers,"100")-SUMIF(Rangewithnumbers,"200") =COUNTIF(Rangewithnumbers,"100")-COUNTIF(Rangewithnumbers,"200") Though you may want: =SUMIF(Rangewithnumbers,"=100")-SUMIF(Rangewithnumbers,"200") =SUMIF(Rangewithnumbers,"100")-SUMIF(Rangewithnumbers,"=200") =SUMIF(Rangewithnumbers,"=100")-SUMIF(Rangewithnumbers,"=200") boundaries issues.... HTH, Bernie MS Excel MVP "JulesHR" wrote in message ... how do i doa formula that will count or sum a bunch of numbers that are between a particular range. i.e. 100 but < 200 ? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif & Sumif
Ok, I get it thanks. Now what I want to do is add all the values in the range
i.e. if there was a number of 150 then 50 would be counted in the range of 100 to 200. Does that make sense? "Bernie Deitrick" wrote: Jules, =SUMIF(Rangewithnumbers,"100")-SUMIF(Rangewithnumbers,"200") =COUNTIF(Rangewithnumbers,"100")-COUNTIF(Rangewithnumbers,"200") Though you may want: =SUMIF(Rangewithnumbers,"=100")-SUMIF(Rangewithnumbers,"200") =SUMIF(Rangewithnumbers,"100")-SUMIF(Rangewithnumbers,"=200") =SUMIF(Rangewithnumbers,"=100")-SUMIF(Rangewithnumbers,"=200") boundaries issues.... HTH, Bernie MS Excel MVP "JulesHR" wrote in message ... how do i doa formula that will count or sum a bunch of numbers that are between a particular range. i.e. 100 but < 200 ? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif & Sumif
Would your desired result for 125 be 25 or 75? 150 is 50 greater than 100, and 50 less than 200,
so I'm not sure what you want... Either =Number - LowerLimit or =UpperLimit - Number will give you the answer you want. HTH, Bernie MS Excel MVP "JulesHR" wrote in message ... Ok, I get it thanks. Now what I want to do is add all the values in the range i.e. if there was a number of 150 then 50 would be counted in the range of 100 to 200. Does that make sense? "Bernie Deitrick" wrote: Jules, =SUMIF(Rangewithnumbers,"100")-SUMIF(Rangewithnumbers,"200") =COUNTIF(Rangewithnumbers,"100")-COUNTIF(Rangewithnumbers,"200") Though you may want: =SUMIF(Rangewithnumbers,"=100")-SUMIF(Rangewithnumbers,"200") =SUMIF(Rangewithnumbers,"100")-SUMIF(Rangewithnumbers,"=200") =SUMIF(Rangewithnumbers,"=100")-SUMIF(Rangewithnumbers,"=200") boundaries issues.... HTH, Bernie MS Excel MVP "JulesHR" wrote in message ... how do i doa formula that will count or sum a bunch of numbers that are between a particular range. i.e. 100 but < 200 ? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif & Sumif
Ok, I get it thanks. Now what I want to do is add all the values in the range
i.e. if there was a number of 150 then 50 would be counted in the range of 100 to 200, or if 125 was the number then 25 would be counted. Does that make sense? "Teethless mama" wrote: For count: =SUM(COUNTIF(A:A,{"100","=200"})*{1,-1}) For sum: =SUM(SUMIF(A:A,{"100","=200"})*{1,-1}) "JulesHR" wrote: how do i doa formula that will count or sum a bunch of numbers that are between a particular range. i.e. 100 but < 200 ? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif & Sumif
for 125 it should count 25. The formula you gave in you last post, where
would they be entered? I don't follow. "Bernie Deitrick" wrote: Would your desired result for 125 be 25 or 75? 150 is 50 greater than 100, and 50 less than 200, so I'm not sure what you want... Either =Number - LowerLimit or =UpperLimit - Number will give you the answer you want. HTH, Bernie MS Excel MVP "JulesHR" wrote in message ... Ok, I get it thanks. Now what I want to do is add all the values in the range i.e. if there was a number of 150 then 50 would be counted in the range of 100 to 200. Does that make sense? "Bernie Deitrick" wrote: Jules, =SUMIF(Rangewithnumbers,"100")-SUMIF(Rangewithnumbers,"200") =COUNTIF(Rangewithnumbers,"100")-COUNTIF(Rangewithnumbers,"200") Though you may want: =SUMIF(Rangewithnumbers,"=100")-SUMIF(Rangewithnumbers,"200") =SUMIF(Rangewithnumbers,"100")-SUMIF(Rangewithnumbers,"=200") =SUMIF(Rangewithnumbers,"=100")-SUMIF(Rangewithnumbers,"=200") boundaries issues.... HTH, Bernie MS Excel MVP "JulesHR" wrote in message ... how do i doa formula that will count or sum a bunch of numbers that are between a particular range. i.e. 100 but < 200 ? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Countif & Sumif
Jules,
Let's say that you enter the 125 into cell A2. In another cell, use the formula =A2-100 HTH, Bernie MS Excel MVP "JulesHR" wrote in message ... for 125 it should count 25. The formula you gave in you last post, where would they be entered? I don't follow. "Bernie Deitrick" wrote: Would your desired result for 125 be 25 or 75? 150 is 50 greater than 100, and 50 less than 200, so I'm not sure what you want... Either =Number - LowerLimit or =UpperLimit - Number will give you the answer you want. HTH, Bernie MS Excel MVP "JulesHR" wrote in message ... Ok, I get it thanks. Now what I want to do is add all the values in the range i.e. if there was a number of 150 then 50 would be counted in the range of 100 to 200. Does that make sense? "Bernie Deitrick" wrote: Jules, =SUMIF(Rangewithnumbers,"100")-SUMIF(Rangewithnumbers,"200") =COUNTIF(Rangewithnumbers,"100")-COUNTIF(Rangewithnumbers,"200") Though you may want: =SUMIF(Rangewithnumbers,"=100")-SUMIF(Rangewithnumbers,"200") =SUMIF(Rangewithnumbers,"100")-SUMIF(Rangewithnumbers,"=200") =SUMIF(Rangewithnumbers,"=100")-SUMIF(Rangewithnumbers,"=200") boundaries issues.... HTH, Bernie MS Excel MVP "JulesHR" wrote in message ... how do i doa formula that will count or sum a bunch of numbers that are between a particular range. i.e. 100 but < 200 ? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF/COUNTIF | Excel Discussion (Misc queries) | |||
| COUNTIF or SUMIF or ?? | Excel Worksheet Functions | |||
| Countif and sumif | Excel Discussion (Misc queries) | |||
| {} SumIf and CountIf | Excel Worksheet Functions | |||
| Countif, Sumif | Excel Worksheet Functions | |||
Linear Mode
