Revenue structure

HelloDharmendra_Bharwad​, 

You can solve both termination and fee update logic with one formula that works in Excel and Google Sheets.

Assumptions
Row 2 contains real month-end dates (Jan 2026 to Dec 2027)
Column A = Client Name
Columns B onward = Monthly revenue
AA = Start Date
AB = Termination Date (blank if active)
AC = Base Fee
AD = Fee Change Date
AE = New Fee

Formula to enter in B3 and copy across and down:

=IF(OR(B$2<$AA3,AND($AB3<>"",B$2>=$AB3)),0,IF(AND($AD3<>"",B$2>=$AD3),$AE3,$AC3))

Logic explanation

Start control
If the month is before the Start Date, revenue is zero.

Termination logic
If a Termination Date exists and the month is on or after that date, revenue becomes zero.
All future months automatically remain zero. No manual clearing required.

Fee update logic
If the month is on or after the Fee Change Date, the New Fee applies.
Earlier months remain at the Base Fee.
The updated fee flows automatically into the next year unless changed again.

Total column formula

=SUM(B3:Y3)

This structure automatically updates totals, requires no manual zeroing, and works in both Excel and Google Sheets.

The challenge is to make these formulas dynamic so that when a termination date or fee change is entered, all future months automatically update without manual intervention. Also, the solution should work in both Excel and Google Sheets.

I think using a combination of IF statements and date comparisons would work. For termination, the formula in each month's revenue cell would check if the termination date is before or equal to the month in the column header. If yes, return zero; else, use the previous month's revenue (or the original value if it's the first month).

I think the solution involves setting up a table with client names, termination dates, fee change dates, and then using formulas that reference these dates to determine the revenue for each month.

To implement the termination and fee update logics in Excel/Google Sheets with automatic future-month adjustments, you can follow this structured approach:

Formula for Monthly Revenue (e.g., Cell B3 for Client A, Jan 2026):

=IF(B$2 >= $C3, 0,

   IF(ROW(B3)=ROW($B$3), $D3,

   IF(B2=0, 0, B2)))

Annual Total (e.g., Cell N3):

=SUM(B3:M3)

Fee Update Logic Implementation

Setup Fee Schedule Table (e.g., Columns O:R):

Formula for Monthly Fee (e.g., Cell D3 for Client A):

=IFERROR(

   XLOOKUP(B$2, $O:$O, $R:$R, $D$2, 0, 1),

   $D$2)

Replace XLOOKUP if in Google Sheets is unavailable.

=IFERROR(VLOOKUP(B$2, $O:$R, 4, 1), $D$2)

Date Formatting: Ensure termination dates (Column C) and fee change dates (Column O) are stored as YYYY-MM-DD (e.g., 2026-07-01) for reliable comparisons.

Fee Schedule Sorting: If using VLOOKUP, sort the fee schedule table by date in ascending order. XLOOKUP doesn’t require sorting.

Total Column: Use =SUM(B3:M3)—it will dynamically exclude zeroed months post-termination.

I Think this approach is scalable, maintainable, and error-resistant. Test it with a small dataset first to confirm behavior, then scale to your full dataset. For Google Sheets, prioritize XLOOKUP or exact-match VLOOKUP to avoid lookup errors.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.