Match Index that SUMS multiple criteria

[pumpkinyoon]

Hello!

I've looked everywhere, but still haven't found the solution! I want to be able to use the match index function to provide the SUM of the results.

Here's what my data looks like, in a simplified form:

My data sheet: (Defined Name is 'StyleExport') **note there are thousands of styles in here, but I want the sum of just 2 of the styles.

	WKS:	1	2
STYLE#	METRICS:
12345	Retail Sales	100	120
12345	Retail Units	5	6
67890	Retail Sales	300	330
67890	Retail Units	10	11

<tbody>
</tbody>

I've defined the names for the cells - Column A: 'Style', Column B: 'Metrics', Row 1: 'Time'

Basically, I want to my formula to return the SUM of retail sales for weeks 1, 2, etc for style #12345 & 67890.

	A	B
1	STYLE:	12345
2		67890
3	WK 1	WK 2
4	RETAIL SALES	=MYFORMULA
5	UNIT SALES

<tbody>
</tbody>

So currently, my formula is rather LONG:
=INDEX(STYLEEXPORT,MATCH(B1,STYLE,0),MATCH(A3,TIME,0)+INDEX(STYLEEXPORT,MATCH(B2,STYLE,0),MATCH(A3,TIME,0))

Instead of having to manually ADD these index matches, is there something I can do to condense the formula?!

I'm looking for something like INDEX(STYLEEXPORT,MATCH(OR(B1,B2),STYLE,0),MATCH(A3,TIME,0))
But that didn't seem to work!

Please let me know!!

 

[RickXL]

Using your first set of example data this should work:

=SUMPRODUCT((B3:B8="Retail Sales")*((A3:A8=12345)+(A3:A8=67890))*C3:D8)

If you define B3:B8 as Metrics, A3:A8 as Style and C3:D8 as Amount then you could change the above to:

=SUMPRODUCT((Metrics="Retail Sales")*((Style=12345)+(Style=67890))*Amount)