Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum 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!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum multiple lookups?
=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 "paula k" wrote: 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!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum multiple lookups?
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 "paula k" wrote: 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! :) "Toppers" wrote: =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 "paula k" wrote: 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!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum multiple lookups?
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! :) "Toppers" wrote: =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 "paula k" wrote: 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!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum multiple lookups?
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 paula k wrote: 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!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum multiple lookups?
Try toppers's(?) second response once more.
paula k wrote: 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! :) "Toppers" wrote: =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 "paula k" wrote: 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!! -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum multiple lookups?
=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) paula k wrote: 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! :) "Toppers" wrote: =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 "paula k" wrote: 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
get a count from multiple lookups | Excel Worksheet Functions | |||
multiple column lookups | Excel Worksheet Functions | |||
Multiple V Lookups | Excel Worksheet Functions | |||
Multiple Lookups | Excel Discussion (Misc queries) | |||
multiple lookups - xls2003 | Excel Worksheet Functions |