Sumproduct from another sheet

[vaesir]

Hi guys,
I've trying to solve this issue to display the sum of all the products but I keep get an Value Error.
This is on sheet 1

	this should be the sum of Sheet 2 colum D
Product	sum
Product 1
Product 2
Product 3

DATA is on Sheet 2

Product	not requ	not req	sum
Product 1	£ 1.00	£ 1.00	£ 10.00
Product 2	£ 2.00	£ 2.00	£ 11.00
Product 3	£ 3.00	£ 3.00	£ 12.00
Product 1	£ 58.00	£ 58.00	£ 5.00
Product 2	£ 4,774.00	£ 4,774.00	£ 6.00
Product 3	£ 66.00	£ 66.00	£ 7.00
Product 1	£ 58.00	£ 58.00	£ 8.00
Product 2	£ 4,774.00	£ 4,774.00	£ 9.00
Product 3	£ 66.00	£ 66.00	£ 20.00

I've tried =COUNTIFS('Sheet2'!a2:a29,'Sheet 1'Product 1,'Sheet2'!D2d19,Sheet1 Product 1)

 

[JEC]

=SUMPRODUCT((Sheet2!$A$1:$A$10=A1)*(Sheet2!$D$1:$D$10))

or

=SUMIF(Sheet2!$A$1:$A$10,A1,Sheet2!$D$1:$D$10)

 

[Dave Patton]

Sumifs.xlsm
	B	C
8	Product 1	23
9
zList

Cell Formulas
Range	Formula
C8	C8	=SUMIFS(Sheet2!D2:D10,Sheet2!A2:A10,B8)

Ensure Column has numbers not text.

 

[vaesir]

=SUMPRODUCT((Sheet2!$A$1:$A$10=A1)*(Sheet2!$D$1:$D$10))

or

=SUMIF(Sheet2!$A$1:$A$10,A1,Sheet2!$D$1:$D$10)

SUMIF worked.
Thank you