SUMIF between dates

Hi Community! I'm just trying total amount of "Booked" from Mon to Sat for the last couple weeks. Any help would be appreciated. Thank you...


Tags:

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @tgattsh,

    To calculate the sum of "Booked" from Monday to Saturday for the past two weeks, please first add a helper column named [Day of the Week]. In this column, use a formula to determine the day of the week for each date. and use the following formula in this column and convert it to column format formula:

    =IFERROR(WEEKDAY(Date@row), "")
    

    then in the sum column use the following formula it will calculate the sum of Booked from Mon to Sat for the last couple weeks:

    =IFERROR(SUMIFS(Booked:Booked, [Day Of the week]:[Day Of the week], AND(@cell <> 1, @cell <> 7), Date:Date, OR(@cell <= TODAY(), @cell >= TODAY(-14))), "")
    

    the following screenshot shows the result:


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • tgattsh
    tgattsh ✭✭✭✭✭

    @Bassam Khalil I really appreciate your response! The suggestion didn't work but I did find a workaround. using,

    It's not pretty, but it worked.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    Hi @tgattsh ,

    It's good news that you found a solution by yourself. Regarding my formula, I tested it and it worked fine. I think there may be something wrong with how it was applied in your sheet. I'm not sure if you created the helper column [Day of the Week].

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!