Formula Help

Options

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


Tags:

Answers

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Options

    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

  • Kristine Gagne, CSM, CSSGB
    Options

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

  • Kristine Gagne, CSM, CSSGB
    edited 12/06/23
    Options

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

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!