MurrayBarn
New Member
- Joined
- May 27, 2012
- Messages
- 32
- Office Version
- 365
- Platform
- Windows
Is there a simpler way of doing the below? I have 12 workbooks called CMN etc. Each one has a simple profit and loss statement in it with 12 columns (starting in F for January, G for February etc) for each month of the year. The below formula is taken from my summary sheet where I am looking up the account code in column A, finding it in the workbooks CMN to Western and adding any numbers that meet the criteria - in this case for January.
I now have this formula replicated about 120 rows down and 12 columns right. It would be great to clean it up. Maybe using something like Indirect() or whatever works better.
=SUMIF([CMN.xlsx]Sheet1!$E:$E,$A11,[CMN.xlsx]Sheet1!F:F)+
SUMIF([ENI.xlsx]Sheet1!$E:$E,$A11,[ENI.xlsx]Sheet1!F:F)+
SUMIF([Teskom.xlsx]Sheet1!$E:$E,$A11,[Teskom.xlsx]Sheet1!F:F)+
SUMIF([ForeignP.xlsx]Sheet1!$E:$E,$A11,[ForeignP.xlsx]Sheet1!F:F)+
SUMIF([Angola.xlsx]Sheet1!$E:$E,$A11,[Angola.xlsx]Sheet1!F:F)+
SUMIF([IntP.xlsx]Sheet1!$E:$E,$A11,[IntP.xlsx]Sheet1!F:F)+
SUMIF([MarineLog.xlsx]Sheet1!$E:$E,$A11,[MarineLog.xlsx]Sheet1!F:F)+
SUMIF([MERP.xlsx]Sheet1!$E:$E,$A11,[MERP.xlsx]Sheet1!F:F)+
SUMIF([Congo.xlsx]Sheet1!$E:$E,$A11,[Congo.xlsx]Sheet1!F:F)+
SUMIF([PGS.xlsx]Sheet1!$E:$E,$A11,[PGS.xlsx]Sheet1!F:F)+
SUMIF([Cem.xlsx]Sheet1!$E:$E,$A11,[Cem.xlsx]Sheet1!F:F)+
SUMIF([Western.xlsx]Sheet1!$E:$E,$A11,[Western.xlsx]Sheet1!F:F)
I now have this formula replicated about 120 rows down and 12 columns right. It would be great to clean it up. Maybe using something like Indirect() or whatever works better.
=SUMIF([CMN.xlsx]Sheet1!$E:$E,$A11,[CMN.xlsx]Sheet1!F:F)+
SUMIF([ENI.xlsx]Sheet1!$E:$E,$A11,[ENI.xlsx]Sheet1!F:F)+
SUMIF([Teskom.xlsx]Sheet1!$E:$E,$A11,[Teskom.xlsx]Sheet1!F:F)+
SUMIF([ForeignP.xlsx]Sheet1!$E:$E,$A11,[ForeignP.xlsx]Sheet1!F:F)+
SUMIF([Angola.xlsx]Sheet1!$E:$E,$A11,[Angola.xlsx]Sheet1!F:F)+
SUMIF([IntP.xlsx]Sheet1!$E:$E,$A11,[IntP.xlsx]Sheet1!F:F)+
SUMIF([MarineLog.xlsx]Sheet1!$E:$E,$A11,[MarineLog.xlsx]Sheet1!F:F)+
SUMIF([MERP.xlsx]Sheet1!$E:$E,$A11,[MERP.xlsx]Sheet1!F:F)+
SUMIF([Congo.xlsx]Sheet1!$E:$E,$A11,[Congo.xlsx]Sheet1!F:F)+
SUMIF([PGS.xlsx]Sheet1!$E:$E,$A11,[PGS.xlsx]Sheet1!F:F)+
SUMIF([Cem.xlsx]Sheet1!$E:$E,$A11,[Cem.xlsx]Sheet1!F:F)+
SUMIF([Western.xlsx]Sheet1!$E:$E,$A11,[Western.xlsx]Sheet1!F:F)