CODEWITHSUNDEEP
excelindexingsubtotal
Brian
Brian

Reputation: 83

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

Upvotes: 1

Views: 2611

Answers (3)

Chronocidal
Chronocidal

Reputation: 7951

A late answer, but one that doesn't use a Helper Column.

By using Matrix Multiplication (MMULT) on a TRANSPOSEd array of whether the Row is larger than itself and the list itself, we can produce an array of the Running Total.

MMULT(--(TRANSPOSE(ROW(D2:D21))<=ROW(D2:D21)), D2:D21)

If we shrink this to just the first 3 rows, to demonstrate, then you are making this calculation:

[[--(2<=2)][--(3<=2)][--(4<=2]     [[10,000]
 [--(2<=3)][--(3<=3)][--(4<=3]  ∙   [ 8,000]
 [--(2<=4)][--(3<=4)][--(4<=4]]     [ 6,000]]

Which gives us this:

[[1][0][0]     [[10,000]    [[10,000]
 [1][1][0]  ∙   [ 8,000]  =  [18,000]
 [1][1][1]]     [ 6,000]]    [24,000]]

We can then compare that against the target value as a condition and divide by the result to eliminate values with #DIV0! errors, and use AGGREGATE to get the smallest non-error value

=AGGREGATE(15, 6, Row(D2:D21) / (MMULT(--(TRANSPOSE(ROW(D2:D21))<=ROW(D2:D21)), D2:D21)<30000), 1)

This will give us the first row where the Running Total is >= $30,000

Upvotes: 1

Brian
Brian

Reputation: 83

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.

Upvotes: 0

S. Roose
S. Roose

Reputation: 1657

make a cumulative row, only adding up if column B equals 'Design'

If you want to be able to do a vlookup, you should make an extra column that checks if amount exceeded 30k, and then output anything that will be your key for the vlookup.

Upvotes: 0

Related Questions