Running Conditional Sum Based on Month

tps49

New Member
Joined
Oct 7, 2014
Messages
26
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

New Member
Joined
Oct 7, 2014
Messages
26
I've tried a couple of sumif and vlookup nested formulas, just can't figure out the right combo.
 

tps49

New Member
Joined
Oct 7, 2014
Messages
26
I realize that clarification is probably confusing haha I can explain if necessary
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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

Active Member
Joined
Feb 26, 2014
Messages
344
Try (A2:L2 data, A4 your input cell)

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

Forum statistics

Threads
1,082,316
Messages
5,364,483
Members
400,802
Latest member
RichBRich

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top