Deferred Revenue for Saas Company

If a) I've understood {which incl. calendar based qtrs} and, b) you're using O365 (per profile) then one option, to simplify, might be as follows:

Formula:

RevData!

J3: ="^"&TEXTJOIN("^",,INDEX(TEXT(DATE(YEAR(B3),MONTH(B3)+(ROW(A$1:INDEX(A:A,$F3))-1),1),"yymm"),0))&"^"

copied down for all rows



Financials!

E6: =SUMPRODUCT(ISNUMBER(SEARCH("^"&TEXT(DATE(2000+MID(E$4,3,2),3*LEFT(E$4)-{2,1,0},1),"yymm")&"^",RevData!$J$3:$J$929))*RevData!$H$3:$H$929/RevData!$F$3:$F$929)

copied across remaining Qtr cells

the above would generate total revenue of $2,028,062 in 2019 and $2,652,485 in 2020
{the above balances matched a much larger matrix based check I did - so seems sound to me - the remaining delta reflects the booked revenue deferred into 2021}