Running Conditional Sum Based on Month

[tps49]

Hello all,

I'm trying to structure a running conditional sum based on a monthly cash flow stream. To clarify, picture three elements:

- A stream of twelve cash flows in one row, each one corresponding to a month
- A list of twelve months in another row
- A single cell that acts as a switch for the user to indicate which month they want the report to run

I want the formula to say, "Take whatever month the user inputs (this could be the name of the month, number corresponding to the month, etc.) and add the cash flows from all of the preceding months, including the input month, and display the result." For instance, if I put "6" or "June", I would want the total of the initial six cash flows to display.

What is the cleanest way to structure this formula? Please don't hesitate to let me know if there's anything I can clarify.

Thank you very much in advance for your help.

 

[tps49]

I've tried a couple of sumif and vlookup nested formulas, just can't figure out the right combo.

 

[tps49]

I realize that clarification is probably confusing haha I can explain if necessary

 

[Aladin Akyurek]

A2:L2 houses the cash flows.

Swith cell, i,e., A4, says 6.

B4:

=SUM(OFFSET(INDEX(A2:L2,A4),0,0,1,-A4))

 

[billszysz]

Try (A2:L2 data, A4 your input cell)

=SUM(A2:INDEX(A2:L2,A4))

 

[tps49]

A2:L2 houses the cash flows.

Swith cell, i,e., A4, says 6.

B4:

=SUM(OFFSET(INDEX(A2:L2,A4),0,0,1,-A4))

This one worked perfectly. Thanks all.

 

[Aladin Akyurek]

This one worked perfectly. Thanks all.

You are welcome. By the way, the INDEX version would work equally well.