Results 1 to 4 of 4

Thread: Question: How to SUM a variable number of VLOOKUP calls

  1. #1

    Question: How to SUM a variable number of VLOOKUP calls



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello, I am trying to create a tool based in excel to be used by some data entry people in our company. I have two tables one that defines valid part names and their corresponding prices (sheet 1). And the other which is used to compose specific sale groups (sheet 2).

    I would like to have the cost in sheet 2 determined by a sum of the prices of the parts in the row. I know I can use VLOOKUP to get the value of each part from sheet 1. However part of the goal is that both sheet 1 and sheet 2 will grow. So I want a formula for cost that takes into account that new parts and prices will be added to sheet 1 and that more parts can be added to sheet 2.

    Any tips or help would be much appreciated.

    Sheet 1 Sheet 2
    Part Name
    Price
    Valve 12
    Pipe 6
    Lens 20
    Name
    Cost
    Part 1
    Part 2
    Part 3
    Sale 1 ? Valve Pipe
    Sale 2 ? Pipe
    Sale 3 ? Lens Pipe Valve
    Sale 4 ? Pipe Pipe

  2. #2
    Formatting got messed up.

    Here is an example Excel File. Examle.xlsx

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,595
    Articles
    0
    Excel Version
    365
    Array-Enter (using Ctrl+Shift+Enter, not just Enter on the keyboard) the following formula in cell B2:
    =SUMPRODUCT(IF($C2:$E2=Prices!$A$2:$A$4,Prices!$B$2:$B$4,0))
    then copy down.

  4. #4
    Thanks, that worked great!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •