Summary of Hours Formula
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
Best Answer
-
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
Answers
-
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
-
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?
-
The Misc category can get a little trickier. How many would you be pulling the total for and how many are there total?
-
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??
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!