Google Sheets SUM until a condition fails - Stack Overflow

admin管理员组

文章数量:1287580

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

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

• google-sheets
• google-sheets-formula

Share Improve this question Follow edited Feb 24 at 4:57 Cole asked Feb 23 at 19:33 ColeCole 13866 bronze badges 4

• 1 It isn't enough to just show the table. You need to explain the logic required to get the Total in detail. Be specific, and clearly indicate which cells are manually entered and which you need to calculate with a formula. – doubleunary Commented Feb 23 at 21:23
• @doubleunary ahh good point, edited to add the formulas – Cole Commented Feb 23 at 21:54
• @Cole Still question is unclear? Can you please put a screenshot of your sample data so that we can see actual scenario? Also which column is your desired output? Column C or column D? – Harun24hr Commented Feb 24 at 2:16
• Hmm I see my wording was pretty vague now. I'm specifically looking for formulas for the C column. The D column is just a total that uses the C column and B column. I updated the question and included a link to the example sheet – Cole Commented Feb 24 at 5:00

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 3

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 --removed for violating best practice rules--

I hope this helps

本文标签： Google Sheets SUM until a condition failsStack Overflow