Sum values from a vlookup

  • Thread starter Thread starter leemit
  • Start date Start date
L

leemit

I have a list/column of company names that are receiving product.
Within those companies, some are are forfeiting certain quantities of
the product to another one of the companies. I want to know in total
what each company is receiving.

ColB ColG ColH
Abc co. Wxy co 10
Def co. Rst co 5
Jki co. Rst co 40

The vlookup is simple enough, lookup the company name in column B and
find in column G and return the quantity in column H. The company in
column G may be receiving product from multiple companies so how do I
get the vlookup to sum the returned matches (i.e. Rst co = 45)?

Or should this be a different function? (There are many columns in
the worksheet so I cannot change to a pivot table for my purposes).
 
Enter in row 1 of Col I (or what is available)
=Sumproduct(--(G$1:G$100=B1),(H$1:H$100))
and copy down

It will give you the sum in H where G is equal to B in that row... B1 for
row 1, B2 for row 2.

Adjust 100 to end of your data set
 
Back
Top