COUNTIFS, Dates + 7, Checkbox Unchecked

Andrea Westrich

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

Paul Newcome

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

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

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

Andrea Westrich

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

Paul Newcome

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.

Andrea Westrich

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.

Paul Newcome

Are you able to provide a screenshot of the actual data, or is it the same as that last screenshot you posted?

Andrea Westrich

Absolutely @Paul Newcome.

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)

Andrea Westrich

@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...

Paul Newcome

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

Andrea Westrich

@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

Paul Newcome

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.

Andrea Westrich

@Paul Newcome thank you so very much! I truly appreciate it.

Paul Newcome

Happy to help. 👍️