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

Kristine Gagne, CSM, CSSGB

I will give this a shot. Thank you so much!

Kristine Gagne, CSM, CSSGB

@Jason Albrecht - I had to modify a bit but yes, it does work

Jason Albrecht

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?