Help with OFFSET/MATCH/SUMIFS formula for YTD figures

[fatha]

I have attached the working file, I need the YTD ( Year to date ) value on changing of the month. with multiple criteria.

For eg: if I give the criteria, October, Div1, Original budget, then I should get the sum of original budget form JAN to OCT of DIV1. If I change to November, then I should get Jan to Nov, etc

Can someone help me please ?

[Richard Buttrey]

Hi and welcome to the forum.

There's no file attached. Please note the paperclip icon is not working. You'll need to choose 'Go Advanced' on the bar at the bottom of the post, then underneath the post choose the 'Manage Attachments' option and proceed from there.

[Pete_UK]

You can use this array* formula in cell R4:

=SUM(INDEX(C3:C8,MATCH(1,(A3:A8=P4)*(B3:B8=Q4),0)):INDEX(C3:N8,MATCH(1,(A3:A8=P4)*(B3:B8=Q4),0),MATCH(R3,C2:N2,0)))

*NOTE that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual < Enter >.

Hope this helps.

Pete

[Pete_UK]

Note also that your month names have spaces in front of most of them (all except January), so you will need to remove those.

Pete

[fatha]

Wow!, Thank you so much, It works

[Pete_UK]

Glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete