COUNTIFS Formula to pull submissions entered on a Weekly Basis (Sun - Sat)

Hello,

I would appreciate anyone who can please help with the formula I have below:

=COUNTIFS({Facility}, "Sumner", {Inspection Date}, >=DATE(2020, 8, 9), {Inspection Date}, <=DATE(2020, 8, 9))

Essentially I want to count the number of submissions or rows I have from a specific location from another sheet from Sunday to Saturday (weekly basis).

Thank you,

Best Answer

Answers

  • Thank you Jason! I totally missed that, but thank you!

    I will try the others.


    Best,

  • Jason,

    Would you know if there is a formula to have this generate on a weekly basis instead of changing the dates manually?

    Thanks

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭

    Definitely! Just use the TODAY and WEEKDAY functions as below (work off the basis that =TODAY() - WEEKDAY(TODAY()) is equal to the Saturday of the last week). This would count the number of Sumner projects for the current week. If you'd want to have more control, you can reference other drop-down cells to choose a facility match (change the blue highlight) or a date cell to change the week you are looking at (yellow highlight, reference any date to count that specific date's week).

    =COUNTIFS(Facility:Facility, "Sumner", [Inspection Date]:[Inspection Date], >=(TODAY() - WEEKDAY(TODAY()) + 1), [Inspection Date]:[Inspection Date], <=(TODAY() - WEEKDAY(TODAY()) + 7))


    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!