Summary of Hours Formula

ALEX BOSTROM

Hello SmartSheet Community,

I have SmartSheet I would like to have the total number of hours broken down by month and unit from another sheet.

Pulling from reference sheet:

Does anyone know how to get the total number of hours for all dates in January for each unit?

Also, my second complication is on the main sheet "Misc." is a category that I will need to add the total number of hours for multiple units within the January dates.

Thanks,

Alex Bostrom

University of Utah Health

Paul Newcome

For January 2021, try this...

=SUMIFS({Student Hours}, {Unit}, Unit@row, {Date}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021))

Then for Deb 2021 you can update the

IFERROR(MONTH(@cell), 0) = 1

to

IFERROR(MONTH(@cell), 0) = 2

Paul Newcome

For January 2021, try this...

=SUMIFS({Student Hours}, {Unit}, Unit@row, {Date}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021))

Then for Deb 2021 you can update the

IFERROR(MONTH(@cell), 0) = 1

to

IFERROR(MONTH(@cell), 0) = 2

ALEX BOSTROM

Hey Paul!

This worked! Is it possible for the first sheet category of "Misc" to add multiple units from the reference sheet with different names like hours for "NCC (D 30) & SICU (B 21)" in the month of January?

Paul Newcome

The Misc category can get a little trickier. How many would you be pulling the total for and how many are there total?

ALEX BOSTROM

There are quite a few categories that "Misc." would cover. Would it be easier to pull them over under their tile then add them separately in the sheet summary??

Paul Newcome

It depends on the number and how frequently you anticipate a change. If you have quite a few and anticipate regular changes to that list, then I would suggest an individual pull for each and then summing them.

If you don't anticipate the list changing, and there are only a handful that would be included or excluded, then we could use a single formula to specify which categories to include or exclude.

Really it just depends on personal preference and the details that would drive the overall amount of work.