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

	A​	B​	C​	D​	E​
1​			17-Jan	17-Feb	17-Mar
2​	ABC	Session 1	–	-50	–
3​	ABC	Session 2	-15	15	12
4​	ABC	Session 3	30	200	12
5​	BCD	Session 1	400	–	-300
6​	BCD	Session 2	-50	100	150
7​	BCD	Session 3	–	-50	–
8​	CDE	Session 1	16	300	–
9​	CDE	Session 2	50	100	15
10​	CDE	Session 3	90	-10	5

<tbody>
</tbody>

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
12​	Input Sheet
13​		17-Jan​			17-Feb​			17-Mar​
14​		Session 1	Session 2	Session 3	Session 1	Session 2	Session 3	Session 1	Session 2	Session 3
15​	ABC	–	-15	30	-50	15	200	–	12	12
16​	BCD	400	-50	–	–	100	-50	-300	150	–
17​	CDE	16	50	90	300	100	-10	–	15	5

<tbody>
</tbody>

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

 

[jeongs1]

I tried, but it still gave me the same result. Is there a way to do it with the formula I mentioned earlier?
=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]

I tried, but it still gave me the same result. Is there a way to do it with the formula I mentioned earlier?
=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))

Before trying to get another formula working, we have to figure out why the suggested formula (from post #8) is not working for you. After we accomplish that, we can try to make adjustments to your formula and see if we can get that working.

The only thing that I can think of is that you have an issue being caused by the dates. Referring to post #8, all of the dates are the first of the month (i.e. Jan-17 = 1/1/2017)

If your regional settings are m/d/y then Feb-17 = 2/1/2017
If your regional settings are d/m/y then Feb-17 = 1/2/2017

Again, start off by getting the suggested formula from post #8 working on the exact data from post #8 in a separate worksheet.

 

[jeongs1]

OH!! As you said, my date format wasn't the same. THANKS AGAIN!!!

 

[jeongs1]

now, my manager wants to know if there's a way we can do it with the formula that I've mentioned earlier. Thank you

 

[63falcondude]

now, my manager wants to know if there's a way we can do it with the formula that I've mentioned earlier. Thank you

That's odd that your manager wants a different formula when you have one that works just fine.

INDEX MATCH formulas return a single result after matching values. INDEX MATCH is not a summation formula.

 

[jeongs1]

Yeah.. not sure why but she wants to know if the other formula can be worked out.... Thank you! I'll be looking forward for your response!

 

[63falcondude]

Yeah.. not sure why but she wants to know if the other formula can be worked out.... Thank you! I'll be looking forward for your response!

I don't believe that it can be modified to be used for what you are trying to do here since it is not a summation formula.

Here is another formula that works:

=SUM(IF(($A$2:$A$10=$A15)*($B$2:$B$10=B$14)*($C$1:$E$1<=B$13),$C$2:$E$10)) Ctrl Shift Enter

 

[jeongs1]

OMG!!!! Awesome! Thank you!
Now.. one last stupid/dumb question... Since I'm doing "ctrl shift enter", dragging the formula down doesn't copy the formula. Is there a way to do it rather than doing "ctrl shift enter" to every cells??

 

[63falcondude]

OMG!!!! Awesome! Thank you!
Now.. one last stupid/dumb question... Since I'm doing "ctrl shift enter", dragging the formula down doesn't copy the formula. Is there a way to do it rather than doing "ctrl shift enter" to every cells??

You're welcome.

You only have to hit Ctrl Shift Enter once. This will automatically put {curly brackets} around the formula.

Then you drag the formula down as you would any other formula.