I’m working on a spreadsheet that tracks monthly finances. Each month has a main entry and sometimes requires adjustments. Currently, I add adjustment rows under each month and manually update the SUM formula for the adjustments, which is becoming quite tedious.
I’m looking for a single formula that automatically sums all the rows below that have the word “Adjustment” in column B, stopping once it detects the next month entry. I’ve attempted to find a SUM function that stops when a condition is no longer met, but haven’t had any luck.
Below is a simplified example of my data:
| Month |
Amount |
Adjustments |
Total |
| Jan |
100 |
|
100 |
| Feb |
150 |
-50 |
100 |
|
Adjust |
Reason 1 |
-50 |
| Mar |
100 |
-100 |
0 |
|
Adjust |
Reason 2 |
100 |
|
Adjust |
Reason 3 |
-200 |
Any suggestions on how to simplify this process without relying on fixed adjustment columns? Thanks for your help!
I’ve found a solution that might work well for your situation. You could use an array formula with SUMPRODUCT to dynamically sum the adjustments until the next month entry. Here’s an example:
=SUMPRODUCT(–(B2:B100=“Adjust”)(C2:C100)(MATCH(1,(A2:A100<>“”)*(ROW(A2:A100)>ROW()),0)>ROW(A2:A100)-ROW()))
This formula checks for ‘Adjust’ in column B, multiplies by the corresponding value in column C, and stops when it encounters the next non-blank cell in column A. You’ll need to adjust the range (B2:B100) based on your data set.
Remember to enter it as an array formula by pressing Ctrl+Shift+Enter. This approach eliminates the need for helper columns and adapts to varying numbers of adjustments per month.
I’ve encountered a similar challenge in my financial spreadsheets. One effective solution I’ve found is using a combination of SUMIF and OFFSET functions. Here’s a formula that might work for you:
=SUMIF(OFFSET(B1,1,0,MATCH(“",B:B,-1)-ROW(B1),1),"Adjust”,OFFSET(C1,1,0,MATCH(“*”,B:B,-1)-ROW(B1),1))
This formula dynamically sums the adjustments until it hits the next month entry. It looks for ‘Adjust’ in column B and sums the corresponding values in column C.
To make it even more robust, you could add error handling with IFERROR function. This approach has saved me countless hours of manual updates and reduced errors in my financial tracking.
Remember to adjust the column references based on your specific spreadsheet layout. Hope this helps streamline your process!
hey there! i’ve dealt with this before. have u tried using SUMIFS? it’s pretty neat for this kinda thing. something like:
=SUMIFS(C:C,B:B,“Adjust*”,A:A,“”)
this’ll sum up all the adjustments til it hits a non-blank cell in column A (new month). might need to tweak it a bit for ur setup tho. good luck!