Jun 25 2022 11:38 AM
I am trying to figure the total hours that volunteers worked but some work both weeks and some don't. I have a sample workbook but can't figure out how to upload it. I am using Office 365.
This is sample worksheet 1
And this is sample worksheet 2
How can I get a formula to get the cumulative hours for all 6 volunteers? In my real workbook I have about 50 different volunteers and each worksheet is for a school year ((e.g.: 2017-2018, 2018-2019, etc) and obviously different people are on the different years but some overlap, that is Mary might work 2017-2018 and 2018-2019 but Joe only worked 2018-2019.
I hope my question makes sense. I really don't want to hand calculate the cumulative hours for each of the 50!
Jun 25 2022 03:34 PM
Jun 26 2022 10:25 AM
@Sergei BaklanThank you for helping but I still don't understand how I can get the cumulative hours for people who aren't on the same line on the two worksheets without going through and doing a XLOOKUP one at a time for each person. In my practice sample, Amy is on line 2 in Week 1 Hrs worksheet and line 3 on Week 2 Hrs worksheet. How can I XLOOKUP without going one by one to each worksheet and referencing the name?
Jun 26 2022 11:15 AM
Let me clarify, would you like to have in each next worksheet cumulative time for all previous worksheets plus current one; or you'd like to calculate in separate worksheet cumulative time for all people involved and all periods.
If the latest technique could be as Excel formula: 3D SUMIF for multiple worksheets | Exceljet
Jun 26 2022 02:04 PM
@Sergei BaklanThank you again for taking the time to help me. I did not word my request well, yes, I would like all the cumulative hours for all the volunteers on one separate worksheet. I looked at the reference you gave me but I think I am too much of a beginner to understand how to do it. I have been using Excel for a while and taking the Coursera course from Macquarie U in Sydney, but not really up to speed on XLOOKUP or your reference.
Jun 27 2022 05:56 AM
I see, thank you. When another question - do you have list of all the volunteers for all periods, or the shall be collected from all worksheets? If you have a list that will be easier.
Jun 27 2022 10:05 AM
@Sergei BaklanHow can I upload my excel workbook?
Jun 27 2022 01:00 PM
Jun 27 2022 02:06 PM
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).
Jun 27 2022 02:26 PM
@Sergei BaklanThis is what I am using, minus last names for privacy.
Jun 27 2022 02:27 PM
Jun 28 2022 04:36 AM
SolutionWe 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.
Jun 28 2022 09:21 AM
Jun 28 2022 01:54 PM
@OnTheRoad411370 , you are welcome
Jun 28 2022 04:36 AM
SolutionWe 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.