SUMIF help please

[dasadler]

In my workbook, I would like to do some SUMIFs based on date and tab names. For example, I have tabs for 2009 Schedule, 2010 Schedule, and 2011 Schedule. I also have a tab 'cumulative summary' where I will be doing the SUMIFs.

I cam make it work fine if I use hard formulas like

=SUMIF('2010 SCHEDULE'!A2:A500,'Cumulative Summary'!B59,'2010 SCHEDULE'!M2:M500)
where '2010 SCHEDULE'!A2:A500 is the range, 'Cumulative Summary'!B59 is the criterion, and 2010 SCHEDULE'!M2:M500 is the sum range.

I would like for the SUMIF formula to extract the year from the date in column A of tab 'Cumulative Summary' and use &" Schedule" to make '2010 Schedule'. I tried the following but it doesn't work so I know I am missing something


=SUMIF('year(A59)&" SCHEDULE"'!A2:A500,'Cumulative Summary'!B59,'2010 SCHEDULE'!M2:M500)

Any ideas?

[HansV]

You can use INDIRECT for this:

=SUMIF(INDIRECT("'"&Year(A59)&" SCHEDULE'!A2:A500"),'Cumulative Summary'!B59,INDIRECT("'"&Year(A59)&" SCHEDULE'!M2:M500"))

[dasadler]

Thank you very much Hans. I always forget about INDIRECT - I need to learn more about INDIRECT and INDEX - I think they are a powerful combination. Gotta find a decent tutorial on their usage.

[HansV]

See:

Excel INDIRECT Function Tutorial
INDEX MATCH - Excel Index Function and Excel Match

[dasadler]

Thank you Hans, good tutorials, both.