How do I return two different values for the same criteria using INDEX MATCH

Question

I am working on a spreadsheet at work and came up with a somewhat annoying problem. I have a reference number that has two values with it. E.g. Ref number: 12345 with $450 and $100.

I am trying to do an index match with sum to add them up, but it will only return the $450 value.

This is the formula I am using:

=SUM(INDEX(Sheet2!$A$2:$N$9872,MATCH($A148,Sheet2!$A$2:$A$9872,0),6))

Basically, I have the reference numbers in Sheet 1, in addition to the Reference number with their corresponding values in sheet 2.

Hopefully this makes sense, and thank you in advance for your help.

Answer 1

You want SUMIFS:

=SUMIFS(Sheet2!$F:$F,Sheet2!$A:$A,$A148)