Formula Help

Kristine Gagne, CSM, CSSGB

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”])

Jason Albrecht

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).