 |
 | 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 / New Users / September 2007q; SUMIF how?
How to resolve this: I think I need to implement SumIf here but not clear how, here is what I need to do: Sheet1: A Column: 10,10,10,11,11,11,12,12 B Column: 100,101,110,120,130,140,100,100 C Column: $50, $25, $15, $5, $25, $20,$30,$10 Sheet2: I want to give numbers in B column in Sheet1 as a parameter and sum C column in Sheet1 based on A column: So A1: 100 (one number in B Column in Sheet1) B Column: 10,11,12 (distinct values) C Column; $50,empty,$40 (sums) How can I do this? Try: =SUMPRODUCT((Sheet1!$A$1:$A$8=B1)*(Sheet1!$B$1:$B$8=$A$1)*Sheet1!$C$1:$C$8)  SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk > How to resolve this: > I think I need to implement SumIf here but not clear how, here is what I [quoted text clipped - 15 lines] > > How can I do this? Hi Sandy, Thanks for reply, but why am I multiplying them, I just need to sum C column based on the criteria on A and B columns. > Try: > [quoted text clipped - 19 lines] > > > > How can I do this? Why don't you try the formula first to see if it works? You are multiplying the arrays to convert Boolean values into zeros and ones then you are summing the values in C based on that. Think of it as a SUMIF with multiple criteria and ranges SignatureRegards, Peo Sjoblom > Hi Sandy, > Thanks for reply, but why am I multiplying them, I just need to sum C [quoted text clipped - 25 lines] >> > >> > How can I do this? Hi Jim On sheet2 in cell C1 enter =SUMPRODUCT(--(Sheet1!$B$1:$B$8=Sheet2!$A$1), --(Sheet1!$A$1:$A$8=Sheet2!B1),Sheet1!$C$1:$C$8) and copy down SignatureRegards Roger Govier > How to resolve this: > I think I need to implement SumIf here but not clear how, here is what I [quoted text clipped - 15 lines] > > How can I do this?
|
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.