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
-
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 - Sevan Technology
Smartsheet Aligned Partner
Answers
-
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 - Sevan Technology
Smartsheet Aligned Partner
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!