google-sheetsgoogle-sheets-formula

Google Sheets SUM until a condition fails

I have a sheet which contains monthly amounts which occasionally need to be adjusted for various reasons. Currently I'm adding rows beneath the month for adjustments and then manually referencing the added adjustment rows for the SUM in the Adjustments cell for the month's row. For example, C5 contains a manually written formula =SUM(D6:D7) which references the two adjustment rows with values in D6 and D7.

Ideally I'd like to be able to just have a single formula for the whole C column that automatically sums all of the rows beneath it which have the word "Adjustment" in the B column up until the next normal month row, but I can't find a good way to perform a SUM that stops when the condition (the cell in column B matching "Adjustment") is no longer true. Am I missing an obvious way to do this with a formula? Is there a better way to structure this?

I could of course use Adjustment columns, but the number is variable so I would need a number of additional columns proportional to the maximum number of Adjustments which is annoying.

Example Sheet

1 Month (A) Amount (B) Adjustments (C) Total (D)
2 January 100 =SUM(B2:C2) (100)
3 February 150 =SUM(D4) (-50) 100
4 Adjustment Just Because -50
5 March 100 =SUM(D6:D7) (-100) 0
6 Adjustment One! 100
7 Adjustment Two! -200
8 April 0

Note: Cells with formulas have the actual value of the cell in parentheses after the formula. The formula for the total column was autofilled from D2 so I only included the formula in D2

Solution

  • I have done this before as follows

    Add a column between A and B set B2 to equal "=if(A2="",B1,A2)" Fill down and hide the column.

    In what is now E2 (Total) the following formula should work "=SUMIFS(E2:E15,B2:B15,A2,C2:C15,"Adjustment")"

    I get the following

    Result

    Remember to hide column B I hope this helps