COUNTIFS, Dates + 7, Checkbox Unchecked
Each day, I want to create a running total of unchecked checkboxes for today and the previous 7 days.
For this sheet, I need a Countifs, that will count from the date column and the previous 7 days of unchecked boxes for each column. It will be a total of unchecked boxes for all columns for today + the previous 7 days. (for the first row, it should just count the day).
I know this will be a COUNTIFS + formula, where I am really having problems is how to get it to count today + the last 7 days as of the date in the date column.
Thanks for your help!!
Best Answers
-
You are going to have to write out separate COUNTIFS for each of the checkbox columns so that range sizes are the same then add them together.
=COUNTIFS([1st Checkbox]:[1st Checkbox], @cell <> 1, Date:Date, AND(@cell <= TODAY(), @cell >= TODAY(-7))) + COUNTIFS([2nd Checkbox]:[2nd Checkbox], @cell <> 1, Date:Date, AND(@cell <= TODAY(), @cell >= TODAY(-7)))
-
Right. That is the expected output because you are still referring to TODAY. You will need to replace that bit of criteria with the updated criteria I posted earlier.
AND(@cell <= Date@row, @cell >= Date@row - 7)
Answers
-
You are going to have to write out separate COUNTIFS for each of the checkbox columns so that range sizes are the same then add them together.
=COUNTIFS([1st Checkbox]:[1st Checkbox], @cell <> 1, Date:Date, AND(@cell <= TODAY(), @cell >= TODAY(-7))) + COUNTIFS([2nd Checkbox]:[2nd Checkbox], @cell <> 1, Date:Date, AND(@cell <= TODAY(), @cell >= TODAY(-7)))
-
@Paul Newcome, The formula works great, (many thanks), however, it returns a 5 for every date in the last 2 weeks. I want it to show the total of unchecked boxes for the date on that row and the last 7 days, counting backward for 7 days. So it should show a history of unchecked boxes for the previous 7 days.
I've attached a photo and you should know this is only for the first column. I tried it this way to keep to ensure I was typing everything correctly and minimize my troubleshooting.
Here is my formula and screenshot. (please ignore the last column...that is next).
=COUNTIFS([Walk jobsite to ensure appropriate subs are onsite]:[Walk jobsite to ensure appropriate subs are onsite], @cell <> 1, Date:Date, AND(@cell <= TODAY(-7)))
-
I notice that your criteria in the AND function doesn't match what I had in my last post. Make sure that matches.
Having said that... In your original post you mentioned "Today and previous 7 days" but in your most recent comment you mention (essentially) dates that are between
AND(@cell <= Date@row, @cell >= Date@row - 7)
Regardless of which one you try, please make sure you include both portions of the AND as well as making sure the arguments (less than/greater than) are correct.
-
Thank you @Paul Newcome, I've corrected the formula to read as below. Now I'm getting a 1 for every date in my photo.
=COUNTIFS([Walk jobsite to ensure appropriate subs are onsite]:[Walk jobsite to ensure appropriate subs are onsite], @cell <> 1, Date:Date, AND(@cell <= TODAY(), @cell >= TODAY(-7)))
Is there still something wrong?
You are correct, I want to show the count for today and the previous 7 days. Not in between.
-
Are you able to provide a screenshot of the actual data, or is it the same as that last screenshot you posted?
-
Absolutely @Paul Newcome.
-
Right. That is the expected output because you are still referring to TODAY. You will need to replace that bit of criteria with the updated criteria I posted earlier.
AND(@cell <= Date@row, @cell >= Date@row - 7)
-
@Paul Newcome, that works! Many thanks.
So I can use this in the future, can you please explain in sentence form, what this formula is doing/saying? It helps me process formulas so I can apply them in other areas.
I'd really apprecaite it!
=COUNTIFS([Walk jobsite to ensure appropriate subs are onsite]:[Walk jobsite to ensure appropriate subs are onsite], @cell <> 1, Date:Date, AND(@cell <= Date@row, @cell >= Date@row - 7))
What I mean is something beginning like this: COUNT Walk the jobsite IF...
-
=COUNTIFS([Walk jobsite to ensure appropriate subs are onsite]:[Walk jobsite to ensure appropriate subs are onsite], @cell <> 1, Date:Date, AND(@cell <= Date@row, @cell >= Date@row - 7))
Basically we are saying to count how many rows have the [Walk jobsite to ensure appropriate subs are onsite] unchecked (checked equals "1" and "<>" means "not equal to", so "<> 1" means "not equal to 1" or "unchecked") and where the Date is less than or equal to the date in the Date column on the row of the formula and where the Date is also greater than or equal to the date in the Date column on the row of the formula minus seven days (so seven days prior to the date in the Date column).
-
@Paul Newcome what does the @cell do in this formula? I understand Date@row, and everything else, but I'm not sure what @cell does.
Thanks
-
For the AND function you need "logical statements". That means "this is equal to that" or "this is less than that" etc..
"@cell" allows us to create that "logical statement" by telling the formula to evaluate the corresponding range on a cell by cell basis.
-
@Paul Newcome thank you so very much! I truly appreciate it.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!