COUNTIF and AND formulas

Shawnob1

Need to count a number of times a date is less than or equal to a specific date and equal to a value in another column

For example, I have a column "Schedule Status" that is a drop down with either "Completed" or "Scheduled" as choices. I also have a column for start date.

I want to calculate how many dates are less than or equal to 03/31/20 and meet the criteria "Scheduled"

Scott M

I would insert a new calculating column and drop in the following formula:

=IF([Scheduled Status]1 = "completed", 0, IF([Start Date]1 < 31 / 3 / 20, 0, 1))

Then just have a sum of that column, can hide the calculating column afterwards.

Paul Newcome

Try something like this...

=COUNTIFS([Schedule Status]:[Schedule Status], "Scheduled", [Start Date]:[Start Date], @cell <= DATE(2020, 03, 31))

Scott M

I would insert a new calculating column and drop in the following formula:

=IF([Scheduled Status]1 = "completed", 0, IF([Start Date]1 < 31 / 3 / 20, 0, 1))

Then just have a sum of that column, can hide the calculating column afterwards.

Paul Newcome

Try something like this...

=COUNTIFS([Schedule Status]:[Schedule Status], "Scheduled", [Start Date]:[Start Date], @cell <= DATE(2020, 03, 31))

Shawnob1

Thank you! That did it.

This community is awesome

Paul Newcome

Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one may be found here.