SUMIF accross multiple sheets

[SusaninHouston]

I have a workbook with 1 sheet per month and a YTD total. The 12 monthly sheets hold the same data in the same format, but possibly in a different order each month. (Think sales results for a team of salespeople listed in descending order.)

I want the YTD sheet to sum results for each salesperson across the 12 monthly sheets.

I can get the answer I want using =SUMIF(Sheet 1... ) + SUMIF(Sheet 2...) type language, but my formula is too long.

Is there a neat way to do this?

 

[Aladin Akyurek]

See:

http://test.mrexcel.com/phpbb2/viewtopic.php?t=123314

 

[MMCKAY]

My suggestion is to name the different ranges of data. For example, Sheet1!$A$1:$P:$456 you might name January. After you assign the data that name, you can reference the name January in formulas rather than the sheet name plus cell range.

Quick way to do this is to highlight the range of data, and type in a name in the cell reference box directly above column A. You may use Offset functions to expand the data range to fit a max number of rows if the data range is likely to change.

May not shorten your formula much, but at least makes it easier to see what the formula is doing.

 

[SusaninHouston]

Tried both suggestions -- They both work and they're both Great!

Thanks

 

[Jimean]

Hi Aladin,

I have the same issue and noticed that you have helped many others with the link you provided above. I have clicked on the link from several posts but it does not seem to work any longer. Any chance you could provide the solution again please? Just for reference, all I'm trying to do is sum information in 60+ tabs into a summation lead sheet and all of the data is in the same range in each tab.

Thank you,
Jimean

 

[FDibbins]

Jimean, I think you need to start your own thread with your question