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:

A	B	C	D	E	F	G	H	I	J
					2BF0001	XX			10
					2BF0001	XX			20
					2BF0001	XX			30
					2BF0002	XZ			50

<tbody>
</tbody>

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