Returning/Sum multiple values using index + match array formula

[Meth0dman27]

Hi everyone,

Currently am using an array index + match formula to return a value based on 2 criteria:

{=IFERROR(INDEX(Sheet1!$F$2:$J$1572,MATCH(1,(Sheet1!$F$2:$F$1572=$D1485)*(Sheet1!$G$2:$G$1572=L$1),0),5)*$F1485," ")}

This seems to have worked for me so far. Column F in sheet 1 is the recipe code, column G is the airline, column J is the qty that I want the value(s) of

The problem I run into is when there are multiple values that match both criteria. For example:

[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2BF0001[/TD]
[TD]XX[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2BF0001[/TD]
[TD]XX[/TD]
[TD][/TD]
[TD][/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2BF0001[/TD]
[TD]XX[/TD]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2BF0002[/TD]
[TD]XZ[/TD]
[TD][/TD]
[TD][/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

Using my formula, this would only result in "10" being returned on my next sheet, because that is the first value it will grab. However, I need somehow to find a way to add those 3 values up so that my formula will total 60

Anyone have any ideas? My only other work around is to take my data set on Sheet1 and just use a pivot table to sum up the codes first, then using my index match to that newly created data set, however if possible I'd like to skip that step.

 

[Jonmo1]

Try Sumifs.

=SUMIFS(Sheet1!$J$2:$J$1572,Sheet1!$F$2:$F$1572,$D1485,Sheet1!$G$2:$G$1572,L$1)*$F1485

 

[Meth0dman27]

Ohhhh, now I feel stupid for not seeing that. :/ Thanks!

 

[Jonmo1]

You're welcome