Need a sum if across multiple sheets in an external workbook

Good Day all

I am in need of some assistance. I am attempting a sumif across multiple sheets in an external workbook. Below is my example. It is returning an error #NAME?. Please can you show me where Im going wrong?

In the external workbook I have created a named range called Sheets. The path below is the path of the workbook

=SUMPRODUCT(SUMIF(INDIRECT(" 'X:\All Of Budget BI\All of Budget 2022\All of Library 2022\[Mothership Expense Library 2022.xlsm]" & Sheets & " 'X:\All Of Budget BI\All of Budget 2022\All of Library 2022\[Mothership Expense Library 2022.xlsm]!$F:$F"),Profile!$G$13&$E12,INDIRECT(" 'X:\All Of Budget BI\All of Budget 2022\All of Library 2022\[Mothership Expense Library 2022.xlsm]" & Sheets &" 'X:\All Of Budget BI\All of Budget 2022\All of Library 2022\[Mothership Expense Library 2022.xlsm]!$J:$J")))

Thanks for jumping in. Sheets is a named range in the external workbook and yes it exists in that workbook

Sorry, \i left my old name range in the workbook. That has no range. The named range is in the library workbook (Unless im incorrect). I have attached the amended workbook without the named rage

=SUMPRODUCT(SUMIF(INDIRECT(" 'X:\All Of Budget BI\All of Budget 2022\All of Library 2022\[Mothership Expense Library 2022.xlsm]" & Sheets & " 'X:\All Of Budget BI\All of Budget 2022\All of Library 2022\[Mothership Expense Library 2022.xlsm]!$F:$F"),A8&E11,INDIRECT(" 'X:\All Of Budget BI\All of Budget 2022\All of Library 2022\[Mothership Expense Library 2022.xlsm]" & Sheets &" 'X:\All Of Budget BI\All of Budget 2022\All of Library 2022\[Mothership Expense Library 2022.xlsm]!$J:$J")))

My apologies for the back and forth.

Right so in my words. I am working in the "workbook" spreadsheet on sheet 15. I have 60 sheets in the "Mothership Expense Library" workbook (I only added 5 sheets as an example). The named list (Named manager) is in the "Mothership Expense Library" workbook on the list sheet. The named range is called "sheets" I am trying to do a sumif across all 60 sheets (Named range "Sheets") - In Expense Library to calculate the total sum of the value in cell E11 of the workbook and return the sum it finds in cell G11 of the workbook in sheet 15. The Expense library workbook is an external book at the path "X:\All Of Budget BI\All of Budget 2022\All of Library 2022\[Mothership Expense Library 2022.xlsm]".

I hope this helps. If not I will try give you a better response. I have uploaded the workbook spreadsheet again

I have data and amounts in sheets 1, 2 and 5 in the library workbook. Column F has what it is summing and column J is the amount it is summing. I have attached the workbook again in case you have an older version

Hi

To simplify my need for assistance, I have reattached two workbooks, namely Data (Where im working) and Book1 (The external workbook).

I need to sum the values of the name Peter in Book1 across all the sheets (1-5) and return the result in the Data workbook in cell B7. Below is my formula so far. Sheets is a named range I created in both workbooks.


=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!$B:$B"),INDIRECT("'"&Sheets&"'!$A:$A"),A7))

What I am struggling with is 2 things:

1. If book1 is located on my C drive, where would the link to this workbook be in the formula if the data workbook is not located in the same place. I'm struggling where to put it in the formula.
2. Does the named range (For the sheets 1-5) need to be in the workbook im working, in this case Data workbook or in the external workbook, namely Book1?

Thank you!