Formula Help
For project resource hours that I am trying to tally I am trying to look across multiple date columns, locating all the dates for a specific month, and then based on that, tally the total number of hours in multiple cells that pertain to those dates. I am not quite sure how to go about it.
So far I have some up with (at least in my thought process) although it doesn't currently work. Any help would be much appreciated.
=SUMIFS([Bootcamp 1]:[Bootcamp 1],[Post Bootcamp 1 Week Ending]:[Post Bootcamp 1 Week Ending],[Bootcamp 2]:[Bootcamp 2],[Post Bootcamp 2 Week Ending]:[Post Bootcamp 2 Week Ending],[Bootcamp 3]:[Bootcamp 3],[Post Bootcamp 3 Week Ending]:[Post Bootcamp 3 Week Ending],[Bootcamp 4],[Post Bootcamp 4 Week Ending], IFERROR(MONTH(@cell), 0)=10,[“Bootcamp 1 # of Hours”, “Post Bootcamp 1 # of Hours”, “Bootcamp 2 # of Hours”, “Post Bootcamp 2 # of Hours”, “Bootcamp 3 # of Hours”, “Post Bootcamp 3 # of Hours”, “Bootcamp 4 # of Hours”, “Post Bootcamp 4 # of Hours”])
Answers
-
Hi @Kristine Gagne, CSM, CSSGB
While the following isn't pretty, I've checked and confirmed this does the job:
=IF(MONTH([Bootcamp 1]@row) = 10, [Bootcamp 1 # of Hours]@row, 0) + IF(MONTH([Post Bootcamp 1 Week Ending]@row) = 10, [Post Bootcamp 1 # of Hours]@row, 0) + IF(MONTH([Bootcamp 2]@row) = 10, [Bootcamp 2 # of Hours]@row, 0) + IF(MONTH([Post Bootcamp 2 Week Ending]@row) = 10, [Post Bootcamp 2 # of Hours]@row, 0) + IF(MONTH([Bootcamp 3]@row) = 10, [Bootcamp 3 # of Hours]@row, 0) + IF(MONTH([Post Bootcamp 3 Week Ending]@row) = 10, [Post Bootcamp 3 # of Hours]@row, 0)
There may be others in the community that can come up with a more elegant solution.
Meanwhile, a couple of things to note:
- The IF statements cover your IFERROR by returning a 0 if the month doesn't = 10
- Ideally you would replace the hardcoded '10' for a cell that allows you to select the month desired (depending on what and how you're looking to access and/or display the results).
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
-
I will give this a shot. Thank you so much!
-
@Jason Albrecht - I had to modify a bit but yes, it does work
-
Thanks for the feedback @Kristine Gagne, CSM, CSSGB
Glad I could help.
For the sake of the community, can you please let us know your final formula?
Are you using the formula in the sheet, or in the sheet summary, or elsewhere?
Hope this helps and that you have a great day,
Jason Albrecht MBA, MBus(AppFin), DipFinMgt
LinkedIn profile - Open to work
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!