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

ITALY	100
FRANCE	200
UK	300
ITALY	20
FRANCE	30
UK	60

<tbody>
</tbody>

SheetB

IRELAND	2
UK	5
ITALY	4
FRANCE	8
NORWAY	3
BRAZIL	2

<tbody>
</tbody>

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

ITALY	100	400
FRANCE	200	1600
UK	300	1500
ITALY	20	80
FRANCE	30	240
UK	60	300

<tbody>
</tbody>

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.