Sum function with Index Match

[allemanable]

I have data that shows the number of units sold for a particular title and the corresponding date:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1/31/2014[/TD]
[TD]2/28/2014[/TD]
[TD]3/31/2014[/TD]
[/TR]
[TR]
[TD]Title A[/TD]
[TD]511[/TD]
[TD]65[/TD]
[TD]528[/TD]
[/TR]
[TR]
[TD]Title B[/TD]
[TD]104[/TD]
[TD]7[/TD]
[TD]236[/TD]
[/TR]
[TR]
[TD]Title C[/TD]
[TD]139[/TD]
[TD]6[/TD]
[TD]393[/TD]
[/TR]
</tbody>[/TABLE]


I want to know on what date a title sold x number of units:
For example: Title A sold 500 units on 1/31/2014. I used the this function: INDEX($B$1:$D$1,MATCH(100,$B2:$D2,-1))
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]100[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]Title A[/TD]
[TD]1/31/2014[/TD]
[TD]1/31/2014[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

However, this doesn't work if I want to know when Title A sold 1000 units. I need it to keep a running sum of the sales and find the corresponding date.
I am using Excel 2013. Any guidance would be appreciated.

 

[CalcSux78]

see post #2 http://www.mrexcel.com/forum/excel-...conditional-multiple-lookup-excel-2010-a.html

I think you have your ranges switched up... INDEX($B$2:$D$2,MATCH(100,$B$1:$D$1,-1),0) Note: using -1 returns the number >= 100 so you could get strange results. If you use 0 instead, it must equal what you are searching (100 in this example)

 

[robertdseals]

Can you do something like "=> 100" and "<500"

 

[CalcSux78]

see post #2 http://www.mrexcel.com/forum/excel-...conditional-multiple-lookup-excel-2010-a.html

I think you have your ranges switched up... INDEX($B$2:$D$2,MATCH(100,$B$1:$D$1,-1),0) Note: using -1 returns the number >= 100 so you could get strange results. If you use 0 instead, it must equal what you are searching (100 in this example)

Sorry... the formula is looking for one cell that meets or exceeds 1000(which does not occur) I don't think INDEX/MATCH in this mannor would be the way to go.

 

[CalcSux78]

this worked for me...
=IF($B2>=G2,$B1,IF($B2+$C2>=G2,$C1,IF(SUM($B2:$D2)>=G2,$D1,"DNE")))

the formula was then copied across the next 2 cells so "G2" became "H2".
[TABLE="width: 287"]
<tbody>[TR]
[TD]1[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]100[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/31/2014 [/TD]
[TD]1/31/2014 [/TD]
[TD]3/31/2014 [/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

 

[Domenic]

Is this what you mean?

A1:D4

[TABLE="width: 192"]
<TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]1/31/14
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]2/28/14
[/TD]
[TD="class: xl64, width: 64, bgcolor: transparent, align: right"]3/31/14
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Title A
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]511
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]65
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]528
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Title B
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]236
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Title C
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]139
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]393
[/TD]
[/TR]
</TBODY>[/TABLE]

F1:I4

[TABLE="width: 192"]
<TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]100
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]500
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]1000
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Title A
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1/31/14
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1/31/14
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]3/31/14
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Title B
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1/31/14
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]#N/A
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]#N/A
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Title C
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1/31/14
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]3/31/14
[/TD]
[TD="class: xl66, bgcolor: transparent, align: center"]#N/A
[/TD]
[/TR]
</TBODY>[/TABLE]

G2, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=INDEX($B$1:$D$1,MATCH(TRUE,G$1<=SUBTOTAL(9,OFFSET($B2:$D2,,,,COLUMN($B2:$D2)-COLUMN($B2)+1)),0))

However, if the titles in Column F are not listed in the same order as Column A, try the following formula instead...

G2, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=INDEX($B$1:$D$1,MATCH(TRUE,G$1<=SUBTOTAL(9,OFFSET(INDEX($B$2:$D$4,MATCH($F2,$A$2:$A$4,0),0),,,,COLUMN($B$1:$D$1)-COLUMN($B$1)+1)),0))

Hope this helps!

 

[allemanable]

Thank you Domenic! That worked!

 

[Domenic]

You're very welcome! And thanks for the feedback!