 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Countif & 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 ? For count: =SUM(COUNTIF(A:A,{">100",">=200"})*{1,-1}) For sum: =SUM(SUMIF(A:A,{">100",">=200"})*{1,-1}) > 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 ? 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? > For count: > =SUM(COUNTIF(A:A,{">100",">=200"})*{1,-1}) [quoted text clipped - 4 lines] > > 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 ? 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 > 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 ? 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? > Jules, > [quoted text clipped - 15 lines] > > 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 ? 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 > 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 [quoted text clipped - 21 lines] >> > 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 ? for 125 it should count 25. The formula you gave in you last post, where would they be entered? I don't follow. > 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... [quoted text clipped - 38 lines] > >> > 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 ? 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 > for 125 it should count 25. The formula you gave in you last post, where > would they be entered? I don't follow. [quoted text clipped - 42 lines] >> >> > 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 to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.