SUM formula with XLOOKUP with multiple arrays

[MO11STA]

Hi All

Hopefully this makes sense but I'm trying to create a rolled up view of data which is extracted from multiple workbooks located in different areas, however the format of these workbooks are all the same as these are from one template.

In the Summary workbook, I'm trying to SUM the totals for each month forecast and actuals from the Template workbook. This is the formula I have created so far but this is pulling incorrect data for my actuals as per the images attached. I believe I need to include in this formula the array of either "Forecast" or "Actuals" unless there is a better option which I'm happy to utilise.

=SUM(XLOOKUP(D2,'path file[Template.xlsx]External Budget'!$M$17:$AJ$17,'path file[Template.xlsx]External Budget'!$M$29:$AJ$29)+XLOOKUP(D2,'path file[Template.xlsx]External Budget'!$M$32:$AJ$32,'path file[Template.xlsx]External Budget'!$M$44:$AJ$44))

I am unable to attach the worksheet as I can not install XL2BB on this device, so please do reach out if this does make sense or need more info.

Thanks in advance

 

[awoohaw]

Have you considered using Power Query (you have it with your 365 version)? The Mr Excel forum has a forum for Power Tools as well.

If you are not familiar with this there are plenty of excellent YouTube tutorials, check out the ones by
Mr. Excel, ExcelIsFun, MyOnlineTrainingHub, or Leila Gharani.

Here is one by MyOnlineTrainingHub:

 

[MO11STA]

Have you considered using Power Query (you have it with your 365 version)? The Mr Excel forum has a forum for Power Tools as well.

If you are not familiar with this there are plenty of excellent YouTube tutorials, check out the ones by
Mr. Excel, ExcelIsFun, MyOnlineTrainingHub, or Leila Gharani.

Here is one by MyOnlineTrainingHub:

Hi, I haven't no as these files need to be stored in the separate relative folders

 

[awoohaw]

Please explore. There are ways to include files from other folders, it just may not be as easily automated for the future.
Best wishes.

This video may help:

 

[MO11STA]

Please explore. There are ways to include files from other folders, it just may not be as easily automated for the future.
Best wishes.

This video may help:

Nice, will do. Thanks