3D SUMIF Formula

[kcherevko]

I am working on a project that has a sheet for each week in the year. Within each sheet is varying employee information. On the last "Total" sheet I need it to search for the ID listed in the A column in every worksheet, when found it needs to look at the L column of the same row add it together and put that sum in the total sheet. Below is the formula I have been using and it keep giving me a zero value but no error.

=SUMPRODUCT(SUMIF(INDIRECT("'"&Tab&"'!"&"L2:L300"),A2,INDIRECT("'"&Tab&"'!L2:L300")))

"Tab" is referencing the sheet index list I create for each other sheet with hyperlinks to their corresponding sheets. The sheets are on a weekly bases of 1.13.17:12.31.17
I have tried putting the sheet names in the formula and it errors out. Is there another formula I could be using. An example employee ID that would be listed in A2 is 6174. Do I need to put in the exact employee ID it is looking for rather than the location on the table where the ID is located?

 

[kcherevko]

Tab must be a range housing the names of the relevant sheets. The sum range and the criteria range seem to be the same. Is that intended?

See: https://www.mrexcel.com/forum/excel-questions/119020-sumif-multiple-sheets.html

It is because I need it to look for the ID and the value on all the sheets. I reviewed that and they have it listed mine is as SheetList simply tab but it does reference the verify of tabs

 

[kcherevko]

Check again if the numbers in column L are real numbers
On each sheet: try in an empty cell
=ISNUMBER(L2) and copy down

M.

I did that and it came out as "True" for all the sheets

 

[Marcelo Branco]

I did that and it came out as "True" for all the sheets

hmm...
So we only have to verify that the values in column A in each worksheet match exactly A2 in the Total worksheet.
Assuming the summary worksheet name is Total, try each worksheet
=A2= Total!A$2
copy down
If some results are TRUE the formula should work

M.