pumpkinyoon
New Member
- Joined
- Mar 3, 2014
- Messages
- 1
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.
<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.
<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!!
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!!