Total number of hours by person from a weekly schedule

[[email protected]]

I am doing more work on my weekly time schedule. As this forum has been very helpful in helping me figure out the number of hours a person has worked in the week, I am now struggling to figure out how to total the number of hours a person has worked in a week when the person has taken on different responsibilities and on different days.

I have uploaded a sample of my spreadsheet where, for example, I am trying to total the number of hours Lisa H has worked which totals 18 hours. I would like to see how I can capture these hours by person on another tab.

I welcome any suggestions and possible programming that may be needed.

Thanks, Jeng13

[BadlySpelledBuoy]

Have a look at the last sheet in the attached. Is that what you mean?

BSB

[[email protected]]

Hey BSB,

This is exactly what I needed. I'm trying to understand how your formula works and it is amazing! Thank you so much as this will help me start to work on my next hurdle with this schedule.

Thank you so much, Jeng13

[BadlySpelledBuoy]

Hi Jeng13,

There's no real mystery behind the formula. Just one of those things you either know or you don't.

To try and explain it simply, SUMIF/SUMIFS works in a similar way to filtering data and summing a range within the results.

For example, if your data contained details of all households in Rhode Island and you had details of the relevant city in ColA and number of children in each household in ColB. If you filtered to all addresses in Lincoln and where the number of children present was great than none, summing the result of the visible rows in colB would give you your answer.
EG:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

(To all you Excel pedants out there, yes I know filtering A and summing B without filtering to <>0 would give the same result. I was stuck for a decent example so let it slide!)

For this to work it relies on the ranges being of the same size (in this case, lengthwise).
So this would work:

Please Login or Register  to view this content.

Whereas this would not:

Please Login or Register  to view this content.

These ranges can be two dimensional and therefore cover more than one column as well as more than one row. As long as the ranges are the same size, they can be offset from each other.

In your example, you have employee names in every 4th column starting from colB and hour totals in every 4th column starting from colE. SO by referring to ranges from the first employee name column to the last employee name column and and equally sized sum range starting at the first hours column to the last, Excel does all the hard work and sums up just what you need.

There are FAR better explanations out there than mine so I'd suggest hunting them down if you really want to grasp how this works. A forum search of "Two dimensional SUMIF" may result in something useful. Failing that there are plenty of decent Excel websites out there to help.

Hope that helps a little.

BSB