Match Month and Year and Return Collumn to Right

Hi there,

I have a table of revenues for each month, listed month1, month2, month3, etc.

I want to reorgansise the revenues in a monthly calendar. The formula will match the month and year in the new table and return one cell to the right.

I have put some notes in blue on the sheet for your reference.

Thank you!

Danny

I'm sure there must be better ways, but you can put this formula in B15:

=SUMIFS(INDEX($C$2:$J$10,MATCH($A15,$A$2:$A$10,0),0),INDEX($B$2:$I$10,MATCH($A15,$A$2:$A$10,0),0),">="&DATE($A$14,COLUMNS($B:B),1),INDEX($B$2:$I$10,MATCH($A15,$A$2:$A$10,0),0),"<"&DATE($A$14,COLUMNS($B:B)+1,1))

Apply your Custom Format, then copy across and down to fill your second table.

Hope this helps.

Pete

Hi Pete,

I really appreicate your help.

I actually have a much larger sheet I am working on and extracted the tables that I wanted help with. I have tried to edit your fomula (which runs over 2 sheets) to no avail.

My apologies to ask for your help again, but can you edit the formula where the source table is on sheet Admin and the result calendar is on sheet Revenue.

I have attached a new file for your reference.

Thank you Pete,

Danny

Here's my formula, amended to take account of two sheets and different cell references - put this in B2 of the Revenue sheet:

=SUMIFS(INDEX(Admin!$C$2:$J$10,MATCH($A2,Admin!$A$2:$A$10,0),0),INDEX(Admin!$B$2:$I$10,MATCH($A2,Admin!$A$2:$A$10,0),0),">="&DATE($A$1,COLUMNS($B:B),1),INDEX(Admin!$B$2:$I$10,MATCH($A2,Admin!$A$2:$A$10,0),0),"<"&DATE($A$1,COLUMNS($B:B)+1,1))

then copy across and down as required.

Hope this helps.

Pete

That's wonderful guys. Thank you so much!

Danny

You have 3 different solutions to choose from now. Thanks for marking the thread as Solved.

You might also 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