SUMPRODUCT with multiple criteria

[jordiCT]

I have two set of tables:
1) In the first table I have a series of percentages associated each with a code (e.g. C2, C18, C20):
[TABLE="width: 500"]
<tbody>[TR]
[TD]C2[/TD]
[TD]C18[/TD]
[TD]C20[/TD]
[TD]C12[/TD]
[TD]C5[/TD]
[TD]C6[/TD]
[/TR]
[TR]
[TD]20%[/TD]
[TD]16%[/TD]
[TD]20%[/TD]
[TD]5%[/TD]
[TD]4%[/TD]
[TD]12%[/TD]
[/TR]
</tbody>[/TABLE]

2) In the second table I have values associated with the same codes as in the first table (i.e. C2, C18, etc):
[TABLE="width: 500"]
<tbody>[TR]
[TD]C2[/TD]
[TD]C12[/TD]
[TD]C6[/TD]
[TD]C3[/TD]
[TD]C9[/TD]
[TD]C18[/TD]
[TD]C5[/TD]
[TD]C20[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]234[/TD]
[TD]25[/TD]
[TD]17[/TD]
[TD]27[/TD]
[TD]15[/TD]
[TD]30[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

What I would like to do is to multiply the percentage values in Table 1 by the values in Table 2 that are associated with the same Cx code and sum them all (i.e. 20%*100+16%*15+20%*50, etc.).

I have tried using sumproduct and sumproduct with INDEX MATCH but could not make it work...

Many thanks!

 

[Marcelo Branco]

Welcome to Mr Excel forum

Assuming your data like this

Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

C2​

[/TD]
[TD]

C18​

[/TD]
[TD]

C20​

[/TD]
[TD]

C12​

[/TD]
[TD]

C5​

[/TD]
[TD]

C6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

20%​

[/TD]
[TD]

16%​

[/TD]
[TD]

20%​

[/TD]
[TD]

5%​

[/TD]
[TD]

4%​

[/TD]
[TD]

12%​

[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

C2​

[/TD]
[TD]

C12​

[/TD]
[TD]

C6​

[/TD]
[TD]

C3​

[/TD]
[TD]

C9​

[/TD]
[TD]

C18​

[/TD]
[TD]

C5​

[/TD]
[TD]

C20​

[/TD]
[TD][/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

100​

[/TD]
[TD]

234​

[/TD]
[TD]

25​

[/TD]
[TD]

17​

[/TD]
[TD]

27​

[/TD]
[TD]

15​

[/TD]
[TD]

30​

[/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD]

48,3​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in J2
=SUMPRODUCT(SUMIF(Sheet1!A1:F1,A1:H1,Sheet1!A2:F2)*A2:H2)

Hope this helps

M.

 

[jordiCT]

Welcome to Mr Excel forum

Assuming your data like this

Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

C2​

[/TD]
[TD]

C18​

[/TD]
[TD]

C20​

[/TD]
[TD]

C12​

[/TD]
[TD]

C5​

[/TD]
[TD]

C6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

20%​

[/TD]
[TD]

16%​

[/TD]
[TD]

20%​

[/TD]
[TD]

5%​

[/TD]
[TD]

4%​

[/TD]
[TD]

12%​

[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

C2​

[/TD]
[TD]

C12​

[/TD]
[TD]

C6​

[/TD]
[TD]

C3​

[/TD]
[TD]

C9​

[/TD]
[TD]

C18​

[/TD]
[TD]

C5​

[/TD]
[TD]

C20​

[/TD]
[TD][/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

100​

[/TD]
[TD]

234​

[/TD]
[TD]

25​

[/TD]
[TD]

17​

[/TD]
[TD]

27​

[/TD]
[TD]

15​

[/TD]
[TD]

30​

[/TD]
[TD]

50​

[/TD]
[TD][/TD]
[TD]

48,3​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in J2
=SUMPRODUCT(SUMIF(Sheet1!A1:F1,A1:H1,Sheet1!A2:F2)*A2:H2)

Hope this helps

M.

Hi Marcelo
Thanks a lot. In fact, your formula did not work for me. I wonder if the problem is that I have some cells blank? My data actually looks more like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]C2[/TD]
[TD]C18[/TD]
[TD]C20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20%[/TD]
[TD]16%[/TD]
[TD]20%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]C2[/TD]
[TD]C18[/TD]
[TD]C20[/TD]
[TD]C17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1406[/TD]
[TD]651[/TD]
[TD]509[/TD]
[TD]572[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would like the formula to do the sum multiplication with the values in the second table in row 3. However, I would like a formula that selects the full table (i.e. including the columns with blank cells - for example, columns E to I in the second table).

Many thanks for your help

 

[jordiCT]

OK, I found the mistake! There were some values equal to "blank" and this gave an error. I have sorted it out by giving value 0 to blank cells.

Thanks!
Jordi

 

[Marcelo Branco]

You are welcome. Glad to help

M.