Forum Discussion
How to get total hours worked when people are not the same on each worksheet
We need to add some to the data organization.
1) Named list of sheets which will be summed is added here
It could be at any place of your workbook. If you expand the list don't forget to update the rage in Name Manager
2) Since Total is in different column in different sheets I duplicated it in column AA for each sheet:
3) With that in Cumulative sheet we may use formula to sum sheets.
In C3 is
=SUMPRODUCT( SUMIF( INDIRECT("'" & sheets & "'!" & "A3:A40"), A3, INDIRECT( "'" & sheets & "'!" & "AA3:AA40") ))and drag it down. I used formula exactly as in https://exceljet.net/formula/3d-sumif-for-multiple-worksheets, you may check this post for the explanation how it works.
If what Norman_Glenn suggested doesn't work you may share it on OneDrive or DropBox or like; or to send direct message here (menu is under your avatar at top right).
SergeiBaklanThis is what I am using, minus last names for privacy.
OnTheRoad411370 , you are welcome
- Thank you so much for taking all the time to help me! Because I have probably bitten off more than I can chew, I will have to take some time to understand the formulas you provided. Thank you SO much!
We need to add some to the data organization.
1) Named list of sheets which will be summed is added here
It could be at any place of your workbook. If you expand the list don't forget to update the rage in Name Manager
2) Since Total is in different column in different sheets I duplicated it in column AA for each sheet:
3) With that in Cumulative sheet we may use formula to sum sheets.
In C3 is
=SUMPRODUCT( SUMIF( INDIRECT("'" & sheets & "'!" & "A3:A40"), A3, INDIRECT( "'" & sheets & "'!" & "AA3:AA40") ))and drag it down. I used formula exactly as in https://exceljet.net/formula/3d-sumif-for-multiple-worksheets, you may check this post for the explanation how it works.