Sum portions of a column until a certain value is met

[jkm5z6]

Here's what I need help with. Any advice would be greatly appreciated.

For Segment 1 (Column D), I need a formula which sums the volumes in column B until the SumTotal Volume in Column E is met. Whichever cell in column B is added last to reach the necessary sum, I need the adjacent cell from column A to be input into the yellow box in Column G. I've done the first two by hand as an example. (e.g. Starting at height 0, it took up to a height of 10 to reach the sum volume of 5000.)

For Segment 2, I need the start height to be 1 greater than the end height from segment 1. (e.g. starting at a height of 11, it took up to a height of 16 to reach a volume of 7500)

Please let me know what formulas to put into the yellow boxes.

I would prefer not to use VBA as I'm unfamiliar with it. Thank you for the help.
[TABLE="width: 310"]
<TBODY>[TR]
[TD="class: xl74, width: 21, bgcolor: transparent"][/TD]
[TD="class: xl72, width: 48, bgcolor: transparent"]A
[/TD]
[TD="class: xl72, width: 56, bgcolor: transparent"]B
[/TD]
[TD="class: xl72, width: 15, bgcolor: transparent"]C
[/TD]
[TD="class: xl72, width: 62, bgcolor: transparent"]D
[/TD]
[TD="class: xl72, width: 56, bgcolor: transparent"]E
[/TD]
[TD="class: xl72, width: 80, bgcolor: transparent"]F
[/TD]
[TD="class: xl72, width: 74, bgcolor: transparent"]G
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]1
[/TD]
[TD="class: xl72, bgcolor: transparent"]Height
[/TD]
[TD="class: xl72, bgcolor: transparent"]Volume
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl72, bgcolor: transparent"]Segment
[/TD]
[TD="class: xl72, bgcolor: transparent"]Volume
[/TD]
[TD="class: xl72, bgcolor: transparent"]Start Height
[/TD]
[TD="class: xl72, bgcolor: transparent"]End Height
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]2
[/TD]
[TD="class: xl75, bgcolor: transparent"]0
[/TD]
[TD="class: xl73, bgcolor: transparent"]21
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"]1
[/TD]
[TD="class: xl75, bgcolor: transparent"]5000
[/TD]
[TD="class: xl75, bgcolor: transparent"]0
[/TD]
[TD="class: xl76, bgcolor: yellow"]10
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]3
[/TD]
[TD="class: xl75, bgcolor: transparent"]1
[/TD]
[TD="class: xl73, bgcolor: transparent"]130
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"]2
[/TD]
[TD="class: xl75, bgcolor: transparent"]7500
[/TD]
[TD="class: xl75, bgcolor: transparent"]11
[/TD]
[TD="class: xl76, bgcolor: yellow"]16
[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]4
[/TD]
[TD="class: xl75, bgcolor: transparent"]2
[/TD]
[TD="class: xl73, bgcolor: transparent"]225
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]5
[/TD]
[TD="class: xl75, bgcolor: transparent"]3
[/TD]
[TD="class: xl73, bgcolor: transparent"]322
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]6
[/TD]
[TD="class: xl75, bgcolor: transparent"]4
[/TD]
[TD="class: xl73, bgcolor: transparent"]420
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]7
[/TD]
[TD="class: xl75, bgcolor: transparent"]5
[/TD]
[TD="class: xl73, bgcolor: transparent"]517
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]8
[/TD]
[TD="class: xl75, bgcolor: transparent"]6
[/TD]
[TD="class: xl73, bgcolor: transparent"]615
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]9
[/TD]
[TD="class: xl75, bgcolor: transparent"]7
[/TD]
[TD="class: xl73, bgcolor: transparent"]712
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]10
[/TD]
[TD="class: xl75, bgcolor: transparent"]8
[/TD]
[TD="class: xl73, bgcolor: transparent"]809
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]11
[/TD]
[TD="class: xl75, bgcolor: transparent"]9
[/TD]
[TD="class: xl73, bgcolor: transparent"]904
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]12
[/TD]
[TD="class: xl75, bgcolor: transparent"]10
[/TD]
[TD="class: xl73, bgcolor: transparent"]998
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[TD="class: xl75, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]13
[/TD]
[TD="class: xl75, bgcolor: transparent"]11
[/TD]
[TD="class: xl73, bgcolor: transparent"]1093
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]14
[/TD]
[TD="class: xl75, bgcolor: transparent"]12
[/TD]
[TD="class: xl73, bgcolor: transparent"]1189
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]15
[/TD]
[TD="class: xl75, bgcolor: transparent"]13
[/TD]
[TD="class: xl73, bgcolor: transparent"]1287
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]16
[/TD]
[TD="class: xl75, bgcolor: transparent"]14
[/TD]
[TD="class: xl73, bgcolor: transparent"]1386
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]17
[/TD]
[TD="class: xl75, bgcolor: transparent"]15
[/TD]
[TD="class: xl73, bgcolor: transparent"]1486
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]18
[/TD]
[TD="class: xl75, bgcolor: transparent"]16
[/TD]
[TD="class: xl73, bgcolor: transparent"]1587
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]19
[/TD]
[TD="class: xl75, bgcolor: transparent"]17
[/TD]
[TD="class: xl73, bgcolor: transparent"]1691
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]20
[/TD]
[TD="class: xl75, bgcolor: transparent"]18
[/TD]
[TD="class: xl73, bgcolor: transparent"]1795
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]21
[/TD]
[TD="class: xl75, bgcolor: transparent"]19
[/TD]
[TD="class: xl73, bgcolor: transparent"]1901
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]22
[/TD]
[TD="class: xl75, bgcolor: transparent"]20
[/TD]
[TD="class: xl73, bgcolor: transparent"]2007
[/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[TD="class: xl74, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

 

[texasalynn]

does this work?

Excel 2010
	D	E	F	G	H
1	Segment	Volume	Start Height	End Height
2	1	5000	0	10	5673
3	2	7500	11	16	8028
Sheet1

Cell Formulas
Range	Formula
H2	=SUMPRODUCT(--($A$2:$A$22<=10),($B$2:$B$22))
H3	=SUMPRODUCT(--($A$2:$A$22>10),--($A$2:$A$22<=16),($B$2:$B$22))

 

[AmitBahadur]

you can also use sumifs or sumif array functions

 

[jkm5z6]

I need the formulas that go into G2 & G3 that yield the 10 & 16 that I manually solved here. Those values need to be found by using the values in E2 & E3.

does this work?
Excel 2010

	D	E	F	G	H

<COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]
[TD="bgcolor: #ffffff"]Segment[/TD]
[TD="bgcolor: #ffffff"]Volume[/TD]
[TD="bgcolor: #ffffff"]Start Height[/TD]
[TD="bgcolor: #ffffff"]End Height[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #ffffff"]1[/TD]
[TD="bgcolor: #ffffff"]5000[/TD]
[TD="bgcolor: #ffffff"]0[/TD]
[TD="bgcolor: #ffff00"]10[/TD]
[TD="align: right"]5673[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #ffffff"]2[/TD]
[TD="bgcolor: #ffffff"]7500[/TD]
[TD="bgcolor: #ffffff"]11[/TD]
[TD="bgcolor: #ffff00"]16[/TD]
[TD="align: right"]8028[/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]H2[/TH]
[TD="align: left"]=SUMPRODUCT(--($A$2:$A$22<=10),($B$2:$B$22))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #dae7f5"]H3[/TH]
[TD="align: left"]=SUMPRODUCT(--($A$2:$A$22>10),--($A$2:$A$22<=16),($B$2:$B$22))[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Robert Mika]

I need the formulas that go into G2 & G3 that yield the 10 & 16 that I manually solved here. Those values need to be found by using the values in E2 & E3.

Try:

Excel 2010
	A	B	C	D	E	F	G	H
1	Height	Volume		Segment	Volume	Start Height	End Height
2	0	21		1	5000	0	10	5673
3	1	130		2	7500	11	16	8028
4	2	225
5	3	322
6	4	420
7	5	517
8	6	615
9	7	712
10	8	809
11	9	904
12	10	998
13	11	1093
14	12	1189
15	13	1287
16	14	1386
17	15	1486
18	16	1587
19	17	1691
20	18	1795
21	19	1901
22	20	2007
Sheet1

Cell Formulas
Range	Formula
G2	{=INDEX($A$1:$A$22,MATCH(TRUE,SUBTOTAL(9,OFFSET($B$1,MATCH(F2,$A$2:$A$22,0),0,ROW($B$1:$B$22)-ROW($B$1)+1))>$E2,0))+F2+1}
Press CTRL+SHIFT+ENTER to enter array formulas.

Credit to Aladin for tips on SUBTOTAL formula.

 

[jkm5z6]

That did it. thanks for the help

Try:Excel 2010

	A	B	C	D	E	F	G	H
Height	Volume	Segment	Volume	Start Height	End Height

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5673[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]130[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]7500[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8028[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]322[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]420[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]517[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]615[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]712[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]809[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]904[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]998[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]1093[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1189[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]1287[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]1386[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1486[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]1587[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]1691[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]1795[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]1901[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #dae7f5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #dae7f5"]G2[/TH]
[TD="align: left"]{=INDEX($A$1:$A$22,MATCH(TRUE,SUBTOTAL(9,OFFSET($B$1,MATCH(F2,$A$2:$A$22,0),0,ROW($B$1:$B$22)-ROW($B$1)+1))>$E2,0))+F2+1}[/TD]
[/TR]
</TBODY>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</TBODY>[/TABLE]



Credit to Aladin for tips on SUBTOTAL formula.

 

[Robert Mika]

You are welcome.

 

[minnsa]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 23"]2015[/TD]
[TD="colspan: 23"]2016[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Event Point Value[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Date[/TD]
[TD]27-Jan[/TD]
[TD]24-Feb[/TD]
[TD]31-Mar[/TD]
[TD]28-Apr[/TD]
[TD][/TD]
[TD]3-May[/TD]
[TD]26-May[/TD]
[TD]30-May[/TD]
[TD]30-Jun[/TD]
[TD]18-Jul[/TD]
[TD]25-Jul[/TD]
[TD]28-Jul[/TD]
[TD]1-Aug[/TD]
[TD]8-Aug[/TD]
[TD]22-Aug[/TD]
[TD]25-Aug[/TD]
[TD]29-Sep[/TD]
[TD]10-Oct[/TD]
[TD]27-Oct[/TD]
[TD]7-Nov[/TD]
[TD]24-Nov[/TD]
[TD]5-Dec[/TD]
[TD]8-Dec[/TD]
[TD]26-Jan[/TD]
[TD]23-Feb[/TD]
[TD]29-Mar[/TD]
[TD]26-Apr[/TD]
[TD]7-May[/TD]
[TD]31-May[/TD]
[TD]4-Jun[/TD]
[TD]10-Jun[/TD]
[TD]28-Jun[/TD]
[TD]16-Jul[/TD]
[TD]23-Jul[/TD]
[TD]26-Jul[/TD]
[TD]30-Jul[/TD]
[TD]6-Aug[/TD]
[TD]13-Aug[/TD]
[TD]30-Aug[/TD]
[TD]27-Sep[/TD]
[TD]8-Oct[/TD]
[TD]25-Oct[/TD]
[TD]5-Nov[/TD]
[TD]29-Nov[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Full Name[/TD]
[TD]January Meeting[/TD]
[TD]February Meeting[/TD]
[TD]Machr Meeting[/TD]
[TD]April Meeting[/TD]
[TD]Event 1[/TD]
[TD]Event 2[/TD]
[TD]May Meeting[/TD]
[TD]Event 3[/TD]
[TD]June Meeting[/TD]
[TD]Event 4[/TD]
[TD]Event 5[/TD]
[TD]July Meeting[/TD]
[TD]Event 6[/TD]
[TD]Event 7[/TD]
[TD]Event 8[/TD]
[TD]August Meeting[/TD]
[TD]September Meeting[/TD]
[TD]Event 9[/TD]
[TD]October Meeting[/TD]
[TD]Event 10[/TD]
[TD]November Meeting[/TD]
[TD]Event 11[/TD]
[TD]Event 12[/TD]
[TD]January Meeting[/TD]
[TD]February Meeting[/TD]
[TD]March Meeting[/TD]
[TD]April Meeting[/TD]
[TD]Event 1[/TD]
[TD]May Meeting[/TD]
[TD]Event 2[/TD]
[TD]Event 3[/TD]
[TD]June Meeting[/TD]
[TD]Event 4[/TD]
[TD]Event 5[/TD]
[TD]July Meeting[/TD]
[TD]Event 6[/TD]
[TD]Event 7[/TD]
[TD]Event 8[/TD]
[TD]August Meeting[/TD]
[TD]September Meeting[/TD]
[TD]Event 9[/TD]
[TD]October Meeting[/TD]
[TD]Event 10[/TD]
[TD]November Meeting[/TD]
[TD]Event 11[/TD]
[TD]Event 12[/TD]
[/TR]
[TR]
[TD]Last1[/TD]
[TD]First1[/TD]
[TD]First1 Last1[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last2[/TD]
[TD]First2[/TD]
[TD]First2 Last2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last3[/TD]
[TD]First3[/TD]
[TD]First3 Last3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last4[/TD]
[TD]First4[/TD]
[TD]First4 Last4[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last5[/TD]
[TD]First5[/TD]
[TD]First5 Last5[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last6[/TD]
[TD]First6[/TD]
[TD]First6 Last6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last7[/TD]
[TD]First7[/TD]
[TD]First7 Last7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last8[/TD]
[TD]First8[/TD]
[TD]First8 Last8[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

As you can see from the above table, I am keeping attendance records for a club that I am a member of. An X in a column above indicates that they attended an event and the number in row 2 indicates the number of points that event is worth. Membership is based upon participation. Once a person has gotten 5 points they have achieved membership. In 2015 they are earning their 2016 membership. If they fail to attain 2016 membership in 2015, they can earn points in 2016 toward their 2016 membership (and by very definition they will have gotten their 2017 membership). I have a second sheet below where I have brought over their names and totaled their points for 2015 and 2016 respectively in columns B and C. I then calculate whether they have attained 2016 membership in column D. What I am having difficulty with, is capturing the event that earned their membership (the event once the 5 points was earned), and the date that membership was earned (in columns E and F). Points earned in 2015 and 2016 are not cumulative, meaning that the counters start over at the end of December each year. I am not looking for a VB solution as I am not at all versed in it. I am looking for formulas already available in Excel. Can anyone assist me in developing formulas for columns E and F of the second sheet?


[TABLE="class: grid"]
<tbody>[TR]
[TD]Full Name[/TD]
[TD]2015 Points[/TD]
[TD]2016 Points[/TD]
[TD]2016 Membership[/TD]
[TD]Event that earned membership[/TD]
[TD]Date Membership was Earned[/TD]
[/TR]
[TR]
[TD]First1 Last1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]First2 Last2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]First3 Last3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]First4 Last4[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]9[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]First5 Last5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]First6 Last6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]First7 Last7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]First8 Last8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]