Multiply if

[Giancar]

Hi all,

I have tried to check for solutions but in vain. Hopefully someone can help

I have a 2 different sheet ("sheetA", "sheetB").

SheetA

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ITALY[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]FRANCE[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]ITALY[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]FRANCE[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]

SheetB
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]IRELAND[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]ITALY[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]FRANCE[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]NORWAY[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]BRAZIL[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


Il cell C1 (SheetA), I would like to multiplying Columns B of SheetA and Column B of SheetB, if cell values in Column A are matching.

Result
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ITALY[/TD]
[TD]100[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]FRANCE[/TD]
[TD]200[/TD]
[TD]1600[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]300[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]ITALY[/TD]
[TD]20[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]FRANCE[/TD]
[TD]30[/TD]
[TD]240[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]60[/TD]
[TD]300[/TD]
[/TR]
</tbody>[/TABLE]

Please consider that Column A of SheetA is very long, so I would need a formula that i drag quite easily.

 

[jtakw]

Hi,

Use SUMPRODUCT like so:

Book1
	A	B	C
1	ITALY	100	400
2	FRANCE	200	1600
3	UK	300	1500
4	ITALY	20	80
5	FRANCE	30	240
6	UK	60	300
SheetA

Cell Formulas
Range	Formula
C1	=SUMPRODUCT((SheetB!A$1:A$5000=A1)*(SheetB!B$1:B$5000)*B1)

Book1
	A	B
1	IRELAND	2
2	UK	5
3	ITALY	4
4	FRANCE	8
5	NORWAY	3
6	BRAZIL	2
SheetB

SheetA C1 formula copied down as far as needed, I used range to row 5000, change to higher if needed.

 

[MARK858]

Just another option...

Excel Workbook
	A	B	C
1	ITALY	100	400
2	FRANCE	200	1600
3	UK	300	1500
4	ITALY	20	80
5	FRANCE	30	240
6	UK	60	300
sheetA

 

[AhoyNC]

Another option VLOOKUP.

Excel Workbook
	A	B	C
1	ITALY	100	400
2	FRANCE	200	1600
3	UK	300	1500
4	ITALY	20	80
5	FRANCE	30	240
6	UK	60	300
Sheet A

Excel Workbook
	A	B
1	IRELAND	2
2	UK	5
3	ITALY	4
4	FRANCE	8
5	NORWAY	3
6	BRAZIL	2
Sheet B

 

[Giancar]

Thank you all. All the formulas suggested were working.
At the end I chose to use VLOOKUP formula.