3D Sumproduct not working

[Carys]

Hi everyone,

I have multiple worksheets (Sheet2, Sheet3, etc.) with values in columns B and C that I want to summarise in Sheet1.

I am trying to use a 3D sumproduct function, but it is not working - I'm just getting the #VALUE ! error. I don't have a coding background and have tried cobbling together various formulae and methods, and am just not getting the result I need.

Basically, in Sheet1 I have a list of 100 employee names in column A. Next to each employee name, in column B, I want the total number of days listed against that employee across multiple worksheets. In Sheet2, Sheet3, etc., there are 2 random employee names listed in column B, and their corresponding number of days listed in column C.

Here is the formula I currently have in cell B2 of Sheet1 (to get the total for the first employee, whose name is in cell A2 of Sheet1): =SUMPRODUCT(INDIRECT("'"&Sheets&"'!"&"B2:B3"),"*"&A2&"*",INDIRECT("'"&Sheets&"'!"&"C2:C3"))

- Sheets is the named range comprising of the worksheet names, which is itself derived from =INDEX(Sheetlist, ROW()) where Sheetlist is a Name created with the code =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1))," ")

- B2:B3 is the range for the criteria (employee name) in Sheet2, Sheet3, etc.

- C2:C3 is the range for the values of interest (number of hours) in Sheet2, Sheet3, etc.

- A2 is the cell in Sheet1 that has the employee's name for which I want the formula to return the total number of days.

I would like to use formulae that allow for the totals to be easily updated as more sheets are added.

Thank you in advance for any help you can give.

 

[Special-K99]

Try

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!A$1:A$100"),A1,INDIRECT("'"&Sheets&"'!B$1:B$100")))
Copy down the column

Suggest you use a different named range other than Sheets to be safer, maybe call it tabs or Rng or something.