Trying to do a CountIFS and add a date range

Kimberly Washeleski

I am working on a Summary Sheet and I am trying to add three options together to get a count. I have the first two but not sure how to add a date range. An example is below:

For an Assigned Activity, Status for Q1. So far, I have the Activity Name, the Registration Status but I can’t figure out how to get a date range (since my sheet is year to date) from one column

The Attempt End Date is the column name, but I need a range from 1/1/20 – 3/31/20 and then again from 4/1/20 – 6/31/20. Am doing wrong (like need a separate column)? Thank you

=COUNTIFS([Activity Name]:[Activity Name], "Kronos Employee Series - Open Timecard", [Registration Status]:[Registration Status], "Completed")

Paul Newcome

Try something like this...

=COUNTIFS([Activity Name]:[Activity Name], "Kronos Employee Series - Open Timecard", [Registration Status]:[Registration Status], "Completed", [Attempt End Date]:[Attempt End Date], AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 3, 31)))

Kimberly Washeleski

Paul That worked. Thank you so much. I really appreciate it.

Kimberly Washeleski

I do have another scenario to add to this. I have blanks in the Attempt End Date. Is there a way to add one more thing that says if the cell is blank but meets other criteria to include in the count? Thank you

Paul Newcome

So you want to COUNTIFS

[Activity Name] = "Kronos Employee Series - Open Timecard"

[Registration Status] = "Completed"

[Attempt End Date] = Date Range OR Blank

??

If that is correct, give this a go...

=COUNTIFS([Activity Name]:[Activity Name], "Kronos Employee Series - Open Timecard", [Registration Status]:[Registration Status], "Completed", [Attempt End Date]:[Attempt End Date], OR(@cell = "", AND(@cell >= DATE(2020, 1, 1), @cell <= DATE(2020, 3, 31))))

Kimberly Washeleski

You are my hero. Thank you again.

Paul Newcome

Happy to help! 👍️