I’m working with a spreadsheet that tracks quarterly expenses. Sometimes I need to add correction entries below each quarter. Right now I’m manually creating formulas like =SUM(E4:E5) in my corrections column to add up the modification rows below each quarter entry.
I want to create one formula that can automatically calculate the sum of all rows below the current row where column A contains “Correction” until it hits the next quarter row. The challenge is making the SUM function stop when the condition changes.
Here’s my current setup:
Row
Quarter (A)
Base (B)
Corrections (C)
Final (D)
2
Q1
200
200
3
Q2
300
-75
225
4
Correction
Overcharge
-75
5
Q3
250
-150
100
6
Correction
Error Fix
50
7
Correction
Late Fee
-200
Is there a way to automate the corrections column calculation?
I had this same issue with project tracking sheets. Use SUMIF with INDIRECT to create dynamic ranges that stop when conditions change. Try this formula: =SUMIF(INDIRECT("A"&ROW()+1&":A"&MATCH("Q*",INDIRECT("A"&ROW()+1&":A100"),0)+ROW()),"Correction",INDIRECT("D"&ROW()+1&":D"&MATCH("Q*",INDIRECT("A"&ROW()+1&":A100"),0)+ROW())) in your corrections column. It searches from the row below until it hits the next quarter entry, then sums only correction rows in that range. You’ll probably need to adjust A100 based on your data size, but this way you won’t have to manually update ranges when adding new corrections.
I’ve tackled similar conditional summing problems before. SUMPRODUCT with ROW comparisons usually does the trick here. Yeah, this formula looks intimidating: =SUMPRODUCT((OFFSET(A2,1,0,COUNTA(A:A)-ROW(),1)="Correction")*(ROW(OFFSET(A2,1,0,COUNTA(A:A)-ROW(),1))<MIN(IF(OFFSET(A2,1,0,COUNTA(A:A)-ROW(),1)<>"",ROW(OFFSET(A2,1,0,COUNTA(A:A)-ROW(),1))))))*OFFSET(D2,1,0,COUNTA(A:A)-ROW(),1)) but it’s actually pretty straightforward. It creates a dynamic range starting from your current row and stops when it hits the next non-empty cell in column A. Best part? No need to hardcode ranges like A100 - it adjusts automatically. I drop this formula in the quarter rows and it grabs correction values from column D until it reaches the next quarter boundary.
maybe try using a helper column to mark when you hit the next quarter and adjust the range accordingly? i’d experiment with a combination of sumifs or something. can be a bit confusing, so good luck!