CODEWITHSUNDEEP
excelexcel-formula
Kim
Kim

Reputation: 1

How do I include two columns of data in a sum until a certain value is reached, then sum just one column for the remaining rows?

Say I have an excel data sheet containing dated entries with an ID and two columns for "hours worked", as well as a table in subsequent columns to summarize the data.

Example:

In this example, columns A to D are the raw data, columns I and J are the formulas I currently have, and L and M are the final numbers I want to have.

Column I uses

{=IFERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($I$1:I1,$A$2:$A$15), 0)),"")}

to find unique IDs from column A.

Column J uses

=MIN(SUM((SUMIF($A$2:$A$15,I2,$C$2:$C$15))+(SUMIF($A$2:$A$15,I2,$D$2:$D$15))))

to sum up all the values of columns C and D for the ID contained in column I.

Essentially what I want to have happen though is for column J to sum up all the values of columns C and D until the sum of those values is 50, and after 50 is reached it should only sum the remaining values in column C.

For example, for ID 12345, it should add 25 + 22 + 3, taking the sum to 50, at which point it should sum the remaining values of column C (the rest of cell C4, ignore cell D5 and just keep summing up column C). The final number for 12345 should be 72.

Note: entries will always be made in chronological order.

Upvotes: 0

Views: 417

Answers (1)

tigeravatar
tigeravatar

Reputation: 26640

Best way I can think of to do this is to use a helper column. With this updated helper formula, the data only needs to be sorted by date. In cell E2 use this formula and copy down:

=MAX(INDEX((A$1:A1=A2)*E$1:E1,))+C2+D2*(MAX(INDEX((A$1:A1=A2)*E$1:E1,))+C2<50)

To get the total sum in your summary table, use this formula in cell J2 and copy down:

=MAX(INDEX(($A$2:$A$15=I2)*$E$2:$E$15,))

You can hide the helper column if preferred, or move it to a different sheet.

Upvotes: 0

Related Questions