Excel Magic Trick 1348: Lookup Adding: Exact Match or Approximate Match (SUM Many VLOOKUPs)

Download Files:

Start File and Finished File:

See how to look up multiple items, retrieve the number associated with the items and add the array of numbers. Examples of SUM Many VLOOKUPs:
1. (00:10) Introduction
2. (01:00) Exact Math Lookup for Adding Multiple Costs or Revenues using SUMIFS a& SUMPRODUCT functions.
3. (04:59) Approximate Match Lookup for retrieving multiple prices, which in turn must be multiplied by Number of Items to get Total Revenue, using the LOOKUP and SUMPRODUCT function.
4. (08:47) Exact Math Lookup for Adding Multiple Contractual Costs using SUMIFS a& SUMPRODUCT functions.
5. (10:42) Exact Math Lookup for Adding Multiple Contractual Costs using DSUM function.
6. (12:40) Summary
Question comes from Simona Stamatova at YouTube.
Related Videos:
Excel Magic Trick 320: Lookup Adding: SUMPRODUCT & SUMIF
Basic Excel Business Analytics #08: Total Revenue Calculation: VLOOKUP or LOOKUP/SUMPRODUCT?

[sim]

Any video on excel, if it is Mike's production, gotta be unique. No way. Thanks a zillion, sir.

[LeilaGharani]

I love this video! It's easy to forget some of these techniques - they come in so handy though! LOOKUP formula is generally not high on my list, but it's just perfect for some situations. Your video is a great reminder of that. Thanks Mike.

[drsteele]

That is very clever, using SUMIFS to emulate countless lookups. I was messing around in an effort to eliminate the helper column and I discovered this CSE formula:  

[VS-rhrq]

learnt something new today.Thanks appreciate it

[shawnchapman]

Hi, I have multiple item codes that are duplicates for a purpose. So I am wanting to pull the total to order per item code, which would be the sum of the item codes products to order over three stores. So lets say we needed to order bleach for three target stores, each store needs a different total. Store 1-23, store 2-250, store 3-3000. I have an item codes column and a total to order quantity column. How can I look up the multiple quantities for the same item code, then have it populate the sum as a formula. Then drag and drop to do it for a set of item codes to total the quantities needed.

[deepakagrawal]

Excellent video, Mike. in the second last example, if the contractual rate table is in a different workbook, SUMIFS gives #Value error once the external workbook is closed. Do we have a solution to take care of this? Thanks.

[ilikemtrain]

For the 1st example, If i am using excel 2003 and there is no sumifs, what can I do for the excat match? I just think of using this formula =SUM(IF($B$12:$B$28=$E$15, F15, IF($B$12:$B$28=$E$16, F16, IF($B$12:$B$28=$E$17, F17, F18))))
However, if there are more data, it is impossible to do

[mohamedchakroun]

Thank you mike
We can use insted the a lookup formula isn't?

[DomingosCJM]

Please, do a video importing xml file with Power Query.