Combining Sum-Offset with Match

[erniepoe]

Hi All,

I am working on a YTD formula. In B1 is the month chosen from a dropdown filter, and C3:N3 are where the months are located. Formula 2 below works, where E118 is where the cell of that particular item is, but I want to build a do-it-all formula that i can just drag down and have the formula find the item and then do the YTD formula on, which i have attempted to build in formula 1, but am not having any luck. The value in B82 would be the same as what is in E118 on the 2023 budget tab (in this case "Repairs & Maintenance"), but evidently Excel needs more than just the row number here.

Any suggestions to correct my formula are much appreciated. Thanks so much.




Formula 1: SUM(OFFSET(MATCH(B82,'2023 budget'!A:A,0),0,0,1,MATCH($B$1,$C$3:$N$3,0)))



Formula 2: SUM(OFFSET('2023 budget'!$E$118,0,0,1,MATCH($B$1,$C$3:$N$3,0)))

 

[Peter_SSs]

In B1 is the month chosen from a dropdown filter

Is that a text month name, a 1-12 number or a date formatted to show month only?

What about a small set of dummy data and expected results with XL2BB so that we can see your layout and what you want, as well as being able to copy it to our worksheets for testing?