 |
 | 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 / September 2007Creating a formula for two columns with Criteria
A B 1 10 20 2 10 0 3 10 -5 4 10 15 5 10 0 Alright say you have this as your data field. I know that using the SUMIF function will give me the total of A if B > 0 (or whatever I set the criteria to), BUT how can I get the sum of A AND B if B is greater than 0 I'd use something like: =SUMIF(B:B,">"&0,A:A)+SUMIF(B:B,">"&0) or =SUMIF(B:B,">"&0,A:A)+SUMIF(B:B,">"&0,B:B) > A B > 1 10 20 [quoted text clipped - 6 lines] > function will give me the total of A if B > 0 (or whatever I set the criteria > to), BUT how can I get the sum of A AND B if B is greater than 0  SignatureDave Peterson Try this: =SUMPRODUCT((B1:B5>0)*((B1:B5)+(A1:A5))) SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > A B > 1 10 20 [quoted text clipped - 6 lines] > function will give me the total of A if B > 0 (or whatever I set the criteria > to), BUT how can I get the sum of A AND B if B is greater than 0 Slightly shorter, and more elegant =SUMPRODUCT((B1:B5>0)*A1:B5) > Try this: > [quoted text clipped - 11 lines] > criteria > > to), BUT how can I get the sum of A AND B if B is greater than 0 Yes ... more elegant!<bg> SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Slightly shorter, and more elegant > [quoted text clipped - 19 lines] > > criteria > > > to), BUT how can I get the sum of A AND B if B is greater than 0 |
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.