SUMIF INDEX MATCH for data before and including dates

[Kaelthas]

	A	B	C	D	E	F	G	H	I	J
1	SIZE	ITEM	APR-14	MAY-14	JUN-14		SIZE	ITEM	PERIOD	AMOUNT
2	0.200	SCOTCH	1	20	3		0.200	SCOTCH	MAY-14	20
3	0.375	RUM	4	5	6
4	0.375	VODKA	7	8	9
5	0.250	SCOTCH	10	11	12

<tbody>
</tbody>

<tbody>
</tbody>

Good day all. I'm looking for a formula to sum information based on more than one criteria (in this case, size, item and period) over columns and rows before and including a specific period. Say, the amount of 0.200 Scotch until the end of May 2014, which is 21.

<tbody>
</tbody>

[TABLE="width: 793"]
<tbody>[TR]
[TD]=SUMIFS(INDEX($C$2:$E$5,0,MATCH(I2,$C$1:$E$1,0)),$A$2:$A$5,G2,$B$2:$B$5,H2)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This formula only present the amount for a specific period though.

Any assistance would be greatly appreciated!

 

[Legacy 143009]

I am sure there shuold be a shorter way but this is the best I can do:

=SUMIF(C1:E1,"<="&I2,INDIRECT("C"&SMALL(IF(COUNTIF($G$2,$A$1:$E$6)*COUNTIF($H$2,$B$1:$B$6),ROW($A$1:$E$6)-MIN(ROW($A$1:$E$6))+1),ROW(A1))&":E"&SMALL(IF(COUNTIF($G$2,$A$1:$E$6)*COUNTIF($H$2,$B$1:$B$6),ROW($A$1:$E$6)-MIN(ROW($A$1:$E$6))+1),ROW(A1))))

 

[Kaelthas]

Works like a charm. Thanks very much Flashbond!

 

[Legacy 143009]

Of course, it's an array formula. You should Ctrl+Shift+Enter

 

[Legacy 143009]

I am sure there shuold be a shorter way but this is the best I can do:

=SUMIF(C1:E1,"<="&I2,INDIRECT("C"&SMALL(IF(COUNTIF($G$2,$A$1:$E$6)*COUNTIF($H$2,$B$1:$B$6),ROW($A$1:$E$6)-MIN(ROW($A$1:$E$6))+1),ROW(A1))&":E"&SMALL(IF(COUNTIF($G$2,$A$1:$E$6)*COUNTIF($H$2,$B$1:$B$6),ROW($A$1:$E$6)-MIN(ROW($A$1:$E$6))+1),ROW(A1))))

Here is a shorter version:

=SUMIF(C1:E1,"<="&I2,INDIRECT("C"&MATCH(G2&H2,A:A&B:B,0)&":E"&MATCH(G2&H2,A:A&B:B,0)))

 

[Kaelthas]

Hi Flashbond.

Another one for you, what if I had duplicate items in column A and B? Say if B4 was also "Rum"?

Thanks and regards.

 

[Mike Szczesny]

Can you use this? My answer got 21.

=SUM(IF($A$2:$A$5=$F$2,IF($B$2:$B$5=$G$2,IF($C$1:$E$1<=$H$2,$C$2:$E$5))))

<tbody>
</tbody>

Size	Item	Apr-14	May-14	Jun-14	Size	Item	Period	Amount
0.2	Scotch	1	20	3	0.2	Scotch	May-14	21
0.375	Rum	4	5	6
0.375	Vodka	7	8	9
0.25	Scotch	10	11	12

<colgroup><col span="2"><col><col><col span="5"></colgroup><tbody>
</tbody>