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.

	A	B	C	D	E	F	G
1	Height	Volume		Segment	Volume	Start Height	End Height
2	0	21		1	5000	0	10
3	1	130		2	7500	11	16
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

<TBODY>
</TBODY>

 

[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
1	Segment	Volume	Start Height	End Height
2	1	5000	0	10	5673
3	2	7500	11	16	8028

<COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell 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)) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

 

[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
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

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Array Formulas Cell 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} <THEAD> </THEAD><TBODY> </TBODY> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<TBODY>
</TBODY>

Credit to Aladin for tips on SUBTOTAL formula.

 

[Robert Mika]

You are welcome.

 

[minnsa]

2015

2016

Event Point Value

1

1

1

1

2

2

1

2

1

2

2

1

2

2

2

1

1

2

1

2

1

2

2

1

1

1

1

2

1

2

2

1

2

2

1

2

2

2

1

1

2

1

2

1

2

2

Date

27-Jan

24-Feb

31-Mar

28-Apr

3-May

26-May

30-May

30-Jun

18-Jul

25-Jul

28-Jul

1-Aug

8-Aug

22-Aug

25-Aug

29-Sep

10-Oct

27-Oct

7-Nov

24-Nov

5-Dec

8-Dec

26-Jan

23-Feb

29-Mar

26-Apr

7-May

31-May

4-Jun

10-Jun

28-Jun

16-Jul

23-Jul

26-Jul

30-Jul

6-Aug

13-Aug

30-Aug

27-Sep

8-Oct

25-Oct

5-Nov

29-Nov

Last Name

First Name

Full Name

January Meeting

February Meeting

Machr Meeting

April Meeting

Event 1

Event 2

May Meeting

Event 3

June Meeting

Event 4

Event 5

July Meeting

Event 6

Event 7

Event 8

August Meeting

September Meeting

Event 9

October Meeting

Event 10

November Meeting

Event 11

Event 12

January Meeting

February Meeting

March Meeting

April Meeting

Event 1

May Meeting

Event 2

Event 3

June Meeting

Event 4

Event 5

July Meeting

Event 6

Event 7

Event 8

August Meeting

September Meeting

Event 9

October Meeting

Event 10

November Meeting

Event 11

Event 12

Last1

First1

First1 Last1

X

Last2

First2

First2 Last2

Last3

First3

First3 Last3

Last4

First4

First4 Last4

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

X

Last5

First5

First5 Last5

X

X

X

X

X

X

X

X

X

X

X

X

X

X

Last6

First6

First6 Last6

X

X

Last7

First7

First7 Last7

X

Last8

First8

First8 Last8

X

X

X

<tbody>
</tbody>

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?

Full Name	2015 Points	2016 Points	2016 Membership	Event that earned membership	Date Membership was Earned
First1 Last1	1	0
First2 Last2	0	0
First3 Last3	0	0
First4 Last4	15	9	Y
First5 Last5	7	10	Y
First6 Last6	1	1
First7 Last7	0	1
First8 Last8	2	1

<tbody>
</tbody>