 |
 | 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 / August 2006sum multiple lookups?
Here is an example of my data: Org criticality score Corporate Marketing critical 100 Human Resources critical 100 Corporate Security non-critical 75 Finance non-critical 100 Corporate Marketing critical 50 I need to search column A for a specific org & B for "critical" and sum their values column C. Example lookup corporate marketing & critical would add 100+50 = 150 how would I do this?? THANKS!! =SUMPRODUCT(--(A1:A100="Corporate Marketing"),--(B1:B100="Critical"),C1:C100) "Corporate Marketing" / "Critical" could be placed in cells and literals replace by cell references. Note SUMPRODUCT cannot reference whole columns. HTH > Here is an example of my data: > [quoted text clipped - 14 lines] > > THANKS!! I'm still having a hard time with that....here is the formula I'm using that is close: =SUMPRODUCT(SUMIF('Score Calc'!D4:D10,{"Corporate Marketing"},'Score Calc'!CJ4:CJ10)) I just need to know how to add another part to the array to also look up "Critical" Can you help?? Thanks! :) > =SUMPRODUCT(--(A1:A100="Corporate Marketing"),--(B1:B100="Critical"),C1:C100) > [quoted text clipped - 23 lines] > > > > THANKS!! Use the following rather then SUMIF and change E4:E10 to required range =SUMPRODUCT(--('Score Calc'!D4:D10="Corporate Marketing"),--('Score Calc'!E4:E10="Critical"),('Score Calc'!CJ4:CJ10)) HTH > I'm still having a hard time with that....here is the formula I'm using that > is close: [quoted text clipped - 34 lines] > > > > > > THANKS!! Try toppers's(?) second response once more. > I'm still having a hard time with that....here is the formula I'm using that > is close: [quoted text clipped - 34 lines] > > > > > > THANKS!!  SignatureDave Peterson =SUM(SUMIF('Score Calc'!D4:D10,{"Corporate Marketing","Critical"},'Score Calc'!CJ4:CJ10) If the criteria set is in a range, say, E2:F2... Either: =SUMPRODUCT(SUMIF('Score Calc'!D4:D10,E2:F2,'Score Calc'!CJ4:CJ10) Or: =SUMPRODUCT(--ISNUMBER(MATCH('Score Calc'!D4:D10,E2:F2,0)),'Score Calc'!CJ4:CJ10) > I'm still having a hard time with that....here is the formula I'm using that > is close: [quoted text clipped - 34 lines] >>> >>> THANKS!! Else I can use =sum(if(A2:A6="Corporate Marketing",if(B2:B6="Critical",C2:C6,""))) Enter is as Array Function CTRL+SHIFT+ENTER Thanks Shail > Here is an example of my data: > [quoted text clipped - 14 lines] > > THANKS!! |
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.