Combining Sum-Offset with Match

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
380
Office Version
  1. 365
Platform
  1. Windows
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)))
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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?
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top