Indirect SUMIF formula needed to calculate across multiple tabs and documents

[colinheslop1984]

=SUMIF(INDIRECT("'"&$C$5&"'!c$12:c$25"),$C$9:$C$22,INDIRECT("'"&$C$5&"'!M$12:M$25"))

Currently using this formula to gather information from a spreadsheet which has a large number of tabs, 1 tab = 1 week of the year. The tab name is referred to as c5 in the above formula, the rest is standard criteria.

I have 5 sheets representing 5 locations. I would like to add in to each sheet a total column for all the locations that will work in the same way as it does now, only gathering the information for the weeks selected in C5 but from across all 5 spreadsheets.

Any ideas how to do this? Please respond with formula suggestions

 

[colinheslop1984]

Nobody?

=SUMIF(INDIRECT("'"&$C$5&"'!c$12:c$25"),$C$9:$C$22,INDIRECT("'"&$C$5&"'!M$12:M$25"))

Currently using this formula to gather information from a spreadsheet which has a large number of tabs, 1 tab = 1 week of the year. The tab name is referred to as c5 in the above formula, the rest is standard criteria.

I have 5 sheets representing 5 locations. I would like to add in to each sheet a total column for all the locations that will work in the same way as it does now, only gathering the information for the weeks selected in C5 but from across all 5 spreadsheets.

Any ideas how to do this? Please respond with formula suggestions

 

[ravishankar]

This will get you started

Sub summarize()
For a = 1 To Sheets.Count
Cells(a, 1) = Sheets(a).Name
'Cells(a, 2) = Application.WorksheetFunction.SumIf(Range("C2:C22"), Sheets(a).Range("b2:b22"), ">10")
Next a
MsgBox "complete"
End Sub

running this macro will list all sheet names in Col A. suitably modify the sumif to suit your needs and remove ' before running it. it should calculate the sum and put in col B
Ravi