I have 2 worksheets in Excel. Both have data from a 2 different databases. The first tab has part numbers and costs (could be different day to day), the second has how many times that part number was used in a job. I am trying to add a column to sum the costs of the part number. I've done an Index/Match to find the part number used in the job from the spreadsheet that has the cost information. My problem is I am only getting the first cost in the array, I need a sum of all the costs. Can some one please help me figure out what I'm doing wrong? Thank you in advance.

Below Part Number is Column B and Total Cost is S:
 **Part Number** **Total Savings**
880105-204-DN    $(0.53)
2187             $-   
4558             $-   
020-9057         $(4.05)
TF-100-1017      $-   
472-4391         $17.21 
TF-1800-2029     $-   
W5-5170-1        $0.29 
W5-5170-2        $0.29 
10411            $-   
4558             $-   
880105-583-ft    $3.38 
472-4391         $0.42 
TH-300-0146      $-   

Below is where I am trying to do the formula:

**Item**        **Feb Savings**
880105-204-DN   $(0.53)
020-9057        $(4.05)
472-4391        $17.21     

Here is my formula: =INDEX('FromDB-15'!S:S,(MATCH([@Item], 'FromDB-15'!B:B, 0)))

  • 1
    Welcome to Super User. We are happy to help, but need details about what you are doing so we can. Please give us an example of how your data is laid out, the formula(s) you are working with, and where you are stuck. – CharlieRB Mar 30 '15 at 19:04

An INDEX/MATCH combination retrieves only one value, just as you are seeing. If I were you, I would use the SUMIF function. It works like this: SUMIF(Range to test, Criterion, Range to sum). The third argument is optional. If you leave it off, it will assume that the range you want to summarize is the same as the range to test.

If I understand your example right, you want something like this:

=SUMIF('FromDB-15'!B:B, [@Item], 'FromDB-15'!S:S)
  • Thank you, thank you!!! I tried everything, apparently not correctly! – Kristina Mar 30 '15 at 19:27

A simple pivot table will give it to you in just a few keystrokes:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.