Formula that would roll forward the amount (netting the amounts)

[jeongs1]

So, with a help from great excel experts in this forum, I was able to get the formula that would give me the results for STEP 1. Now, I need help for STEP 2. F

or an example, I want ABC's Session 2 for Feb-17 to be the net value of from both Jan-17 and Feb-17, which would equal to ZERO. Then for Mar-17, ABC's Session 2 would become 12.

Another example would be for ABC's Session 3 for Feb-17 would be 230, netting/accumulating both Jan and Feb. Then for Mar-17, it would become 242, all of Jan, Feb, and Mar.

I feel like "<="&EOMONTH should be used somewhere, but not quite sure how and where I would insert it... Greatly appreciate any help!!!


[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]17-Jan[/TD]
[TD]17-Feb[/TD]
[TD]17-Mar[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]ABC[/TD]
[TD]Session 1[/TD]
[TD]–[/TD]
[TD]-50[/TD]
[TD]–[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]ABC[/TD]
[TD]Session 2[/TD]
[TD]-15[/TD]
[TD]15[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]ABC[/TD]
[TD]Session 3[/TD]
[TD]30[/TD]
[TD]200[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]BCD[/TD]
[TD]Session 1[/TD]
[TD]400[/TD]
[TD]–[/TD]
[TD]-300[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]BCD[/TD]
[TD]Session 2[/TD]
[TD]-50[/TD]
[TD]100[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]BCD[/TD]
[TD]Session 3[/TD]
[TD]–[/TD]
[TD]-50[/TD]
[TD]–[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]CDE[/TD]
[TD]Session 1[/TD]
[TD]16[/TD]
[TD]300[/TD]
[TD]–[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]CDE[/TD]
[TD]Session 2[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]CDE[/TD]
[TD]Session 3[/TD]
[TD]90[/TD]
[TD]-10[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]Input Sheet[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD][/TD]
[TD]

17-Jan​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

17-Feb​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

17-Mar​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD][/TD]
[TD]Session 1[/TD]
[TD]Session 2[/TD]
[TD]Session 3[/TD]
[TD]Session 1[/TD]
[TD]Session 2[/TD]
[TD]Session 3[/TD]
[TD]Session 1[/TD]
[TD]Session 2[/TD]
[TD]Session 3[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]ABC[/TD]
[TD]–[/TD]
[TD]-15[/TD]
[TD]30[/TD]
[TD]-50[/TD]
[TD]15[/TD]
[TD]200[/TD]
[TD]–[/TD]
[TD]12[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]BCD[/TD]
[TD]400[/TD]
[TD]-50[/TD]
[TD]–[/TD]
[TD]–[/TD]
[TD]100[/TD]
[TD]-50[/TD]
[TD]-300[/TD]
[TD]150[/TD]
[TD]–[/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]CDE[/TD]
[TD]16[/TD]
[TD]50[/TD]
[TD]90[/TD]
[TD]300[/TD]
[TD]100[/TD]
[TD]-10[/TD]
[TD]–[/TD]
[TD]15[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


B15=INDEX($C$2:$E$10,MATCH($A15,$A$2:$A$10,0)+MATCH(B$14,$B$2:$B$4,0)-1,MATCH(B$13,$C$1:$E$1,0))

 

[63falcondude]

How's this?

Excel 2010
	A	B	C	D	E	F	G	H	I
1			17-Jan	17-Feb	17-Mar		17-Jan	17-Feb	17-Mar
2	ABC	Session 1	0	-50	0		0	-50	-50
3	ABC	Session 2	-15	15	12		-15	0	12
4	ABC	Session 3	30	200	12		30	230	242
5	BCD	Session 1	400	0	-300		400	400	100
6	BCD	Session 2	-50	100	150		-50	50	200
7	BCD	Session 3	0	-50	0		0	-50	-50
8	CDE	Session 1	16	300	0		16	316	316
9	CDE	Session 2	50	100	15		50	150	165
10	CDE	Session 3	90	-10	5		90	80	85
Sheet1

Cell Formulas
Range	Formula
G2	=SUM($C2:C2)

 

[jeongs1]

That's not going to work since I have Data Sheet and Input Sheet in the format as above... I'd love to change the format around, but can't.

 

[63falcondude]

That's not going to work since I have Data Sheet and Input Sheet in the format as above... I'd love to change the format around, but can't.

I assumed that you were showing us what you have already. Is that not the case?

I was basing my formula off of that top table. If you prefer to base the formula off of the bottom table, you can try this:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1			Jan-17	Feb-17	Mar-17
2	ABC	Session 1	0	-50	-50
3	ABC	Session 2	-15	0	12
4	ABC	Session 3	30	230	242
5	BCD	Session 1	400	400	100
6	BCD	Session 2	-50	50	200
7	BCD	Session 3	0	-50	-50
8	CDE	Session 1	16	316	316
9	CDE	Session 2	50	150	165
10	CDE	Session 3	90	80	85
11
12	Input Sheet
13		Jan-17	Jan-17	Jan-17	Feb-17	Feb-17	Feb-17	Mar-17	Mar-17	Mar-17
14		Session 1	Session 2	Session 3	Session 1	Session 2	Session 3	Session 1	Session 2	Session 3
15	ABC	0	-15	30	-50	15	200	0	12	12
16	BCD	400	-50	0	0	100	-50	-300	150	0
17	CDE	16	50	90	300	100	-10	0	15	5
Sheet1

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(($A$15:$A$17=$A2)*($B$13:$J$13<=C$1)*($B$14:$J$14=$B2)*($B$15:$J$17))

 

[jeongs1]

Thank you!!!

Another quick question. Is there a way to do that with the below formula? Using "<="&EOMONTH inserted somewhere inside last MATCH? Just curious.

B15=INDEX($C$2:$E$10,MATCH($A15,$A$2:$A$10,0)+MATCH(B$14,$B$2:$B$4,0)-1,MATCH(B$13,$C$1:$E$1,0))

 

[63falcondude]

Thank you!!!

Another quick question. Is there a way to do that with the below formula? Using "<="&EOMONTH inserted somewhere inside last MATCH? Just curious.

B15=INDEX($C$2:$E$10,MATCH($A15,$A$2:$A$10,0)+MATCH(B$14,$B$2:$B$4,0)-1,MATCH(B$13,$C$1:$E$1,0))

You're welcome. Happy to help.

My formulas are in C2:E10. They refer to the table on the bottom.

Are you saying that your formulas are in B15:J17 and refer to the top table?

I am confused as to where your data is vs where you want the results to be.

 

[jeongs1]

Sorry for the confusion. Yes, my formulas would go in to B15:J17. The data is the very first table and I want the result to be in the second table where it's labeled as "Input Sheet".

 

[63falcondude]

Sorry for the confusion. Yes, my formulas would go in to B15:J17. The data is the very first table and I want the result to be in the second table where it's labeled as "Input Sheet".

Alright, in that case try this:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1			Jan-17	Feb-17	Mar-17
2	ABC	Session 1	0	-50	0
3	ABC	Session 2	-15	15	12
4	ABC	Session 3	30	200	12
5	BCD	Session 1	400	0	-300
6	BCD	Session 2	-50	100	150
7	BCD	Session 3	0	-50	0
8	CDE	Session 1	16	300	0
9	CDE	Session 2	50	100	15
10	CDE	Session 3	90	-10	5
11
12	Input Sheet
13		Jan-17	Jan-17	Jan-17	Feb-17	Feb-17	Feb-17	Mar-17	Mar-17	Mar-17
14		Session 1	Session 2	Session 3	Session 1	Session 2	Session 3	Session 1	Session 2	Session 3
15	ABC	0	-15	30	-50	0	230	-50	12	242
16	BCD	400	-50	0	400	50	-50	100	200	-50
17	CDE	16	50	90	316	150	80	316	165	85
Sheet1

Cell Formulas
Range	Formula
B15	=SUMPRODUCT(($A$2:$A$10=$A15)*($B$2:$B$10=B$14)*($C$1:$E$1<=B$13)*($C$2:$E$10))

Note that I filled in the blank cells in row 13 with the correct month.

 

[jeongs1]

It gave me "#N/A"

 

[63falcondude]

It gave me "#N/A"

Then your layout (or data) must be different from the example that you gave because as you can see from post #8, it works fine for me.

Try copying and pasting the example from post #8 in a separate worksheet and see if you can get the formula working there before moving on to your actual sheet.