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?

 

[Roger Govier]

Hi
If it were me, I would avoid using separate sheets - then you don't get the 3D problem.

I would add the data fro all weeks to a single sheet starting in say row 5 for the headers, and adding an extra column for week number.
This would allow for applying any totals above the header with simple sub-total formulae.
Turn this data into a Table (shortcut Control T).
Now you can filter for any week number to view just the data for that week, and all of your relevant totals can appear at the top of the sheet.

 

[Marcelo Branco]

See if this works

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

M.

 

[Aladin Akyurek]

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

 

[kcherevko]

Hi,

This is still giving me a "0" value"

 

[Marcelo Branco]

Hi,

This is still giving me a "0" value"

Are you referring to the formula in post 3? It worked for me.
Be sure that:
The values in column A on each sheet match A2 in the Total sheet (no spaces or extraneous characters)
L2:L300 on each sheet contains numbers, not text.

M.

 

[kcherevko]

Are you referring to the formula in post 3? It worked for me.
Be sure that:
The values in column A on each sheet match A2 in the Total sheet (no spaces or extraneous characters)
L2:L300 on each sheet contains numbers, not text.

M.

Column A is just numbers but L is actually a formula but it does product just a number in accounting format. Is that the problem?

 

[Marcelo Branco]

Column A is just numbers but L is actually a formula but it does product just a number in accounting format. Is that the problem?

I don't think so.
If the value in A2 (Total sheet) match any value in column A on any sheet and there is a number in the correspondent row the formula should return a number.
In fact, it could return a zero if there are positive and negative numbers in column L that summed would result in 0. But i presume this is not the case.
Check again if the numbers in column L are real numbers
Try in an empty cell
=ISNUMBER(L2) and copy down

M.

 

[kcherevko]

I don't think so.
If the value in A2 (Total sheet) match any value in column A on any sheet and there is a number in the correspondent row the formula should return a number.
In fact, it could return a zero if there are positive and negative numbers in column L that summed would result in 0. But i presume this is not the case.

M.

You are correct. It is either positive or 0 but there is a value there for everyone.

 

[Marcelo Branco]

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.