Hi, I am trying to summarize multiple years' data into Quarter, Month & YTD, comparing Forecast.
using Sum product Filter function
=SUM(FILTER($C5:$CB5,(TEXT($C$4:$CB$4,"yyyy"))*($C$3:$CB$3=$CG$3),0))
Not sure without seeing what exactly is in the cells you reference but your original formula doesn't seem to make sense. Perhaps this one does what you need.
=SUM(FILTER($C5:$CB5,YEAR($C$4:$CB$4)=$CG$3,0))
It assumes that row 4 contains real dates and CG3 a real number (i.e. not a text)
If it doesn't work, please come back here and attach you file. with some fake amounts for each of the periods.
attached file
I believe that your set-up make matters unnecessarily complicated, but that's your choice. The attached file has the formulas that calculate the sub-total how I think you need them.
Note that I removed the headers on row 4 like "Total 2025", "Total 2026" etc. as these would result in VALUE errors.
@riny Thanks for solving. Any suggestions for a better setup to view Data Quarterly, Month, & YTD?
@vik Well, I would collect all data in a tabular format. For instance, a five column table with the following headers:
Project, Object, Date, Amount, Measure (Actual or Forecast)
No totals or sub-totals and let Excel do the heavy work with (Power)Pivot tables.
