I didn't want to post this in general because it's really not an issue. I just want to see if maybe I can improve on some formulas I just changed. Feel free to critique or ignore. sample workbook included for second one.
Summing the trailing 12 months if it is November(not including November):
=IF(MONTH($E$3:$CO$3)=11,SUMPRODUCT((DATE(YEAR($E$3:$CO$3),MONTH($E$3:$CO$3),1)>=(DATE(YEAR(CN$3),MONTH(CN$3)-12,1)))*(DATE(YEAR($E$3:$CO$3),MONTH($E$3:$CO$3),1)<(DATE(YEAR(CN$3),MONTH(CN$3),1)))*($E7:$CO7)*$C9),0)
If a date is 13 months past the current month and the row is equal to a string multiply the cell by (1/(1-percentage)), then multiply by a second, and third percentage. Then subtract the number without "(1/(1-percentage))":
=(SUMPRODUCT(((DATE(YEAR($C$5:$C$16),MONTH($C$5:$C$16)+12+1,1)>L$3)*L$5:L$16)*($A$5:$A$16=$A18)*(1/(1-$F$5:$F$16)),$G$5:$G$16,$E$5:$E$16)-SUMPRODUCT(((DATE(YEAR($C$5:$C$16),MONTH($C$5:$C$16)+12+1,1)>L$3)*L$5:L$16)*($A$5:$A$16=$A18),$G$5:$G$16,$E$5:$E$16))
Bookmarks