Counting Checkboxes + Date Range

Jordan Ionita

Hi,

I am currently trying to count the amount of Covid Cases and have it report every Wednesday. For example, I need the total number of Covid Cases from 1/5/22-1/12/22 (so the report to show a weeks worth every Wednesday). I have it setup that if someone is Covid+, they check a box. I have the formula to count the Covid+ : =COUNTIF({Astound West COVID Tracker Range 1}, 1), but need to know how to calculate the date ranges within there

Genevieve P.

Hi @Jordan Ionita

The way I would do this is to add a helper column next to your Date column to identify what "week number" the date falls into, based on what day of the week it is and what week of the year it is.

=IF(OR(WEEKDAY(Date@row) = 2, WEEKDAY(Date@row) = 3), IFERROR(IF(WEEKNUMBER(Date@row) - 1 = 0, 52, WEEKNUMBER(Date@row) - 1), ""), IFERROR(WEEKNUMBER(Date@row), ""))

This will then create a "week" where the Start Day of the week is Wednesday:

Then for your COUNT formula, you will want to COUNT the rows where the WeekNumber is Today's Week Number subtract 1, for last week's data:

=COUNTIFS({Astound West COVID Tracker Range 1}, 1, {COVID Tracker WeekNumber}, WEEKNUMBER(TODAY()) - 1)

However, this will check back a week without including a second Wednesday, so if you looked at it tomorrow you would see 1/5/22-1/11/22. Is this what you were looking to do?

Another way to gather this data would be to create a Report that Filters by the checkbox, then Groups the rows by your helper, adjusted WeekNumber formula. You can then use the Summarize function in a Report to see how many rows there are associated with each week.

See: Redesigned Reports with Grouping and Summary Functions

Cheers,

Genevieve

Jordan Ionita

@Genevieve P. , what should I put for the Date and Row? I just tried to put that formula in that you gave and it came up #unparseable; is there something in that formula I need to change based on my sheet?

Genevieve P.

Hi @Jordan Ionita

Date@row is the name of your Date column, and then the @row will stay the same (so it applies the formula for each row). So if your Date column is named "Today's Date" then your formula would look like this:

=IF(OR(WEEKDAY([Today's Date]@row) = 2, WEEKDAY([Today's Date]@row) = 3), IFERROR(IF(WEEKNUMBER([Today's Date]@row) - 1 = 0, 52, WEEKNUMBER([Today's Date]@row) - 1), ""), IFERROR(WEEKNUMBER([Today's Date]@row), ""))

Or if your Date column is named "Date Column", then it would look like this:

=IF(OR(WEEKDAY([Date Column]@row) = 2, WEEKDAY([Date Column]@row) = 3), IFERROR(IF(WEEKNUMBER([Date Column]@row) - 1 = 0, 52, WEEKNUMBER([Date Column]@row) - 1), ""), IFERROR(WEEKNUMBER([Date Column]@row), ""))

Does that make sense?

The formula just says that if the Weekday in the Date Column of this Row is 2 or 3 (so it's Monday or Tuesday), then present the Weeknumber - 1. Otherwise, return the Weeknumber.

If this doesn't work, can you post a screen capture of your sheet, but block out sensitive data?

Cheers,

Genevieve

Jordan Ionita

Genevieve P. , I will post a screenshot. I am thinking because the date that we have chosen also has the time stamp, it may not be working. Would that cause it to have this error??

So I am trying to do this formula off of the "Date Reported" but we have selected to have it time stamped as well. My report needs to count the number of EEs added within the week, with the weeks starting on Wednesday...

thank you for your help! :)

Jordan Ionita

I believe I have figured out the above. I have a new error now though for a different formula I am trying. I am trying to count checkboxes but in an "OR" fashion. (example: we have a check box for "Active Quarantine" and a check box for "Returned from Quarantine"; How do I formulate an OR statement for if one of those two boxes are checked within that date range? I currently have:

=COUNTIFS({Astound West COVID Tracker Range 6 OR {Astound West COVID Tracker Range 7}, 1, {Astound West COVID Tracker Range 6 OR {Astound West COVID Tracker Range 7}}, WEEKNUMBER(TODAY()) - 1)

And am getting an error

Genevieve P.

Hi @Jordan Ionita

The formula should work on a System Column with timestamps, so I'm glad you got it working! You would have just needed to adjust the column name to "Date Reported" in the formula:

=IF(OR(WEEKDAY([Date Reported]@row) = 2, WEEKDAY([Date Reported]@row) = 3), IFERROR(IF(WEEKNUMBER([Date Reported]@row) - 1 = 0, 52, WEEKNUMBER([Date Reported]@row) - 1), ""), IFERROR(WEEKNUMBER([Date Reported]@row), ""))

In regards to your second formula, I would actually write two separate COUNTIFS statements then add them together:

=COUNTIFS({First Checkbox Column}, 1, {Helper Weeknumber Column}, WEEKNUMBER(TODAY()) - 1) + COUNTIFS({Second Checkbox Column}, 1, {Helper Weeknumber Column}, WEEKNUMBER(TODAY()) - 1)

Cheers,

Genevieve