Excel dynamic YTD vs PYTD, based on actuals of this year

Question

I have full year 2023 sales by months and I have Jan-Feb 2024 actual sales by months by categories I would like to have YTD vs PYTD difference % calculated automatically as I input actuals of 2024

For example, next time I enter March 2024 (green range), YTD vs PYTD % difference (yellow cell) should be calculated based on Jan-March 2024 vs Jan-March 2023

I need this to be calculated in one yellow column

I tried

IF(B35>0;SUM($B35:B35)/SUM($B17:B17)-1;0) 

but the problem is I need this dynamic in one column, not 12 columns

I would appreciate any help here

Answer 1

Assuming there is no Excel Constraints and while writing this formula if the Beta Channel is enabled then one could use the following formula:

---

=BYROW(IF(B8:M10<>"",B8:M10,0),SUM)/BYROW(IF(B8:M10<>"",B2:M4,0),SUM)-1

---

or, Using MMULT()

=MMULT(IF(B8:M10<>"",B8:M10,0),SEQUENCE(12)^0)/
 MMULT(IF(B8:M10<>"",B2:M4,0),SEQUENCE(12)^0)-1