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

 

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