Sumproduct with Lookup

[aspence]

I am trying to find the cleanest way to sum the numbers in a column, but only if the corresponding row value in another column is found within a current table. Below is what I am working with.

I need to add up the subtotal column only if the value in the corresponding item column is found within a table "ListPrice". In this example, the first line item "W2430" would be in the table, while the second line item "man-pfg" would not be in the table.

[TABLE="width: 765"]
<colgroup><col span="3"><col span="3"></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]List Price[/TD]
[TD]Multiplier Price[/TD]
[TD="colspan: 3"]Subtotal[/TD]
[/TR]
[TR]
[TD]w2430[/TD]
[TD] $ 400.00[/TD]
[TD] $ 112.00[/TD]
[TD="colspan: 3"] $ 112.00[/TD]
[/TR]
[TR]
[TD]man-pfg[/TD]
[TD] $ 35.00[/TD]
[TD] $ 35.00[/TD]
[TD="colspan: 3"] $ 70.00
[/TD]
[/TR]
</tbody>[/TABLE]


I was thinking about using SumProducts, but could not get what I needed to working. Any ideas?

 

[sheetspread]

sumif as an array will work:

Excel 2010
	A	B	C	D	E	F	G	H
1	Item	List Price	Multiplier Price	Subtotal
2	w2430	$400.00	$112.00	$112.00				w2430
3	man-pfg	$35.00	$35.00	$70.00				zxcvb
4	qwert	12	77	89				vbnm
5	asdfg	58	69	127
6	zxcvb	94	93	187
7	yuiop	77	5	82
8	hjkl	90	79	169
9	vbnm	31	31	62
10
11		361
Sheet4

Cell Formulas
Range	Formula
B11	=SUMPRODUCT(SUMIF($A$2:$A$9,$H$2:$H$4,$D$2:$D$9))

 

[aspence]

That worked great. I was thinking I had to use some form of lookup or vlookup and couldn't make it return what I needed. I was even able to used my named table in the SUMIF formula instead of creating a new list for reference. Thanks.