Search code examples
excelindexingsubtotal

How to find row at which summed total is greater than a certain amount?

I would like to find the row at which running summed value has reached a specified amount and several criteria have been met (similar to sumifs).

I can't just add a cumulative row, as suggested here:

Count rows until the sum value of the rows is greater than a value

....because I have other criteria to meet in the data, and therefore can't have a running total.

In the following dummy example, I'd like to find the date at which the "Design" project has spent or exceeded $30,000

Example Table

Solution

  • Ok, for anyone who is interested, here is what I ended up doing. I created a separate table that had all of my possible weeks (10/17, 10/24, 10/31, etc.) in one column, and corresponding sequential numbers in the next column. I ended up with 54 in my actual project.

    Weeks example

    Then, I ended up having to insert one column into my dataset for the purposes of looking up that "Week No", for each "Week" in all rows. Then on my other sheet where I was solving, I had a cell be my decision variable for the week #. I had another be my target $. I created a formula that took my target amount minus the SUMIFS for all of my criteria (Project, Name, etc.) with the last criteria being that the week number had to be "<=" & (decision cell). I then used Solver to minimize the output by changing the target week with constraints that the target week had to be integer, >=1, <=54, and that the output had to be >=0. That got me to the week prior to where the funding went negative. I then had a cell lookup that week number +1 on my weeks table to find the week at which my target amount would be met.

    Sorry, had to explain it that way, vs. the actual formula, as my actual formula has a lot of SUMIFS criteria and cell references that wouldn't make any sense here.