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):

C2	C18	C20	C12	C5	C6
20%	16%	20%	5%	4%	12%

<tbody>
</tbody>

2) In the second table I have values associated with the same codes as in the first table (i.e. C2, C18, etc):

C2	C12	C6	C3	C9	C18	C5	C20
100	234	25	17	27	15	30	50

<tbody>
</tbody>

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

	A​	B​	C​	D​	E​	F​
1​	C2​	C18​	C20​	C12​	C5​	C6​
2​	20%​	16%​	20%​	5%​	4%​	12%​

<tbody>
</tbody>

Sheet2

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	C2​	C12​	C6​	C3​	C9​	C18​	C5​	C20​		Result​
2​	100​	234​	25​	17​	27​	15​	30​	50​		48,3​

<tbody>
</tbody>

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

	A​	B​	C​	D​	E​	F​
1​	C2​	C18​	C20​	C12​	C5​	C6​
2​	20%​	16%​	20%​	5%​	4%​	12%​

<tbody>
</tbody>

Sheet2

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	C2​	C12​	C6​	C3​	C9​	C18​	C5​	C20​		Result​
2​	100​	234​	25​	17​	27​	15​	30​	50​		48,3​

<tbody>
</tbody>

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:

	A	B	C	D	E	F
1	C2	C18	C20
2	20%	16%	20%

<tbody>
</tbody>

	A	B	C	D	E	F	G	H	I
1	C2	C18	C20	C17
2
3	1406	651	509	572

<tbody>
</tbody>

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.