|
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 ? |
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 ? |
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 ? |
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 ? |
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 ? |
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 ? |
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 ? |
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 ? |
| All times are GMT +1. The time now is 01:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com