 |
 | 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 2008SUMIF Criteria
I'm trying to use the SUMIF function with criteria that value must be >30 and <=60. How would I code that with this function? Use two sumif functions... one adds up everything over 30, the other everything over 60. Subtract to get those in (30-60] =sumif(range,">30",sum_range) - sumif(range,">60",sum_range) > I'm trying to use the SUMIF function with criteria that value must be >30 and > <=60. How would I code that with this function? For example, in A1 thru A5: 29 30 40 60 61 =SUMPRODUCT(A1:A5*(A1:A5>30)*(A1:A5<=60)) displays 100  SignatureGary''s Student - gsnu200784 > I'm trying to use the SUMIF function with criteria that value must be >30 and > <=60. How would I code that with this function? Try using sumproduct. suppose your data was in A1:A100 you would write the formula =Sumproduct((A1:A100>30)*(A1:A100<=60)*A1:A100) > I'm trying to use the SUMIF function with criteria that value must be >30 and > <=60. How would I code that with this function? If your data is in Column A, would this formula work for you? =IF(AND(SUM(A13>30),SUM(A13<=60)),SUM(A13),0) Dan What value do the 3 uses of the SUM() function add to your formula, Dan? What does that formula do that you wouldn't get from =IF(AND(A13>30,A13<=60),A13,0) ? [You may perhaps like to look at Excel help for the SUM function, to remind yourself that SUM takes a list of arguments and SUMs them. If you give the SUM function only one argument, as in each of your 3 cases, the SUM is trivial.] Additionally, neither formula actually addresses the OP's requirement for a SUMIF function, as you haven't actually done a SUM, and you've addressed only one cell. Hence the SUMPRODUCT solution, or subtracting one SUMIF from another, would seem more appropriate. SignatureDavid Biddulph > If your data is in Column A, would this formula work for you? > > =IF(AND(SUM(A13>30),SUM(A13<=60)),SUM(A13),0) > > Dan |
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.