Sumproduct with Lookup

aspence

Board Regular
Joined
Feb 10, 2009
Messages
130
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.

ItemList PriceMultiplier PriceSubtotal
w2430 $ 400.00 $ 112.00 $ 112.00
man-pfg $ 35.00 $ 35.00 $ 70.00

<colgroup><col span="3"><col span="3"></colgroup><tbody>
</tbody>


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

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
sumif as an array will work:


Excel 2010
ABCDEFGH
1ItemList PriceMultiplier PriceSubtotal
2w2430$400.00$112.00$112.00w2430
3man-pfg$35.00$35.00$70.00zxcvb
4qwert127789vbnm
5asdfg5869127
6zxcvb9493187
7yuiop77582
8hjkl9079169
9vbnm313162
10
11361
Sheet4
Cell Formulas
RangeFormula
B11=SUMPRODUCT(SUMIF($A$2:$A$9,$H$2:$H$4,$D$2:$D$9))
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top