ExcelBanter - sum multiple lookups?

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - sum multiple lookups? (https://www.excelbanter.com/excel-worksheet-functions/105614-sum-multiple-lookups.html)

paula k

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!!

Toppers

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!!

Toppers

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!!

paula k

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!!

shail

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!!

Dave Peterson

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

Aladin Akyurek

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!!

All times are GMT +1. The time now is 11:38 AM.