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

Brenda Monroy

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,

Jason Tarpinian

Your formula should already work, but you have a typo in the 3rd criterion (it is the same criterion as the 2nd, it should be 2020,8,15). If you want to make this more dynamic, you can use the TODAY function to grab the week end and beginnings.

Current Week's Sunday: TODAY() - WEEKDAY(TODAY()) + 1

Current Week's Saturday: TODAY() - WEEKDAY(TODAY()) + 7

Jason Tarpinian

Your formula should already work, but you have a typo in the 3rd criterion (it is the same criterion as the 2nd, it should be 2020,8,15). If you want to make this more dynamic, you can use the TODAY function to grab the week end and beginnings.

Current Week's Sunday: TODAY() - WEEKDAY(TODAY()) + 1

Current Week's Saturday: TODAY() - WEEKDAY(TODAY()) + 7

Brenda Monroy

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

I will try the others.

Best,

Brenda Monroy

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

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