Using Vlookup to find several items and then sum these items

**headley4ever** · 12-04-2010, 01:37 PM

Hello Everyone, Can someone help me with this.

Lets say in column "A" i have item numbers for products that i sell, and in column "C" and "D" i have the amount sold for each product. One of these products named "Big Deal" has four (4) item numbers, the item numbers are (101,155,165 & 200) I now need a formula that will look for these item numbers in colum "A" and then sum or total the amount sold for this item.

I tried this formula but its not working: Sumif(A1:D7,(vlookup{101,155,165,200},A1:D7,{3,4},0)),(A1:A7))

Column Column Column Column
A B C D
Row# Item# Product Sold Sold
1. 101 Big Deal 15 20
2. 107 Star Meal 9 10
3. 112 Hot Wings 22 50
4. 150 Popcorn 33 75
5. 155 Big Deal 70 65
6. 165 Big Deal 90 80
7. 200 Big Deal 20 12

Sumif(A1:D7,(vlookup{101,155,165,200},A1:D7,{3,4},0)),(A1:A7))

**zbor** · 12-04-2010, 01:53 PM

In some cell (i.e. E1) write what you looking for (Big Deal).

Then use this formula:

=SUMIF(B1:B7, E1, C1:C7)+SUMIF(B1:B7, E1, D1:D7)

**headley4ever** · 12-04-2010, 02:05 PM

Thanks for your suggestion however can this be done using vlookup?

**headley4ever** · 12-04-2010, 02:11 PM

i really need a formula that will look in column "A' for these 4 item numbers and then sum the amount sold which is in colum "C" & "D"

**martindwilson** · 12-05-2010, 11:09 AM

doesn't zbor's suggestion do just that?
this will work with the numbers
=SUMPRODUCT(($B$2:$B$8=101)+($B$2:$B$8=155)+($B$2:$B$8=165)+($B$2:$B$8=200),INDEX($D$2:D8+$E$2:$E$8, 0))

**DanExel** · 12-06-2010, 02:25 AM

Have you considered using filters along with subtotal funktions? If your application can accommedate this method and you need to know how how I will gladly send you an example.