COUNTIFS formula that compares dates in two columns as a criteria

Steve Fogel · 10/08/19

Here's what I'm trying to do. KPI is a check box field (Key Performance Indicators). Status is a single choice list box field. Actual Finish and Plan End date are date fields. I want to count the number of KPI rows with a check mark where the status is complete and the Actual finish date is on or before the plan end date.

Here is the formula I am using.

=COUNTIFS([KPI]:[KPI], 1, [Status]:[Status], "Complete", [Actual Finish]:[Actual Finish],<=[Plan End Date]:[Plan End Date])

If I remove the date comparison the calc works fine.

Any suggestions.

thanks

Paul Newcome · 10/08/19

You would need to use a helper column to make the date comparison such as a checkbox type column with a basic

=IF([Actual Finish]@row <= [Plan Finish]@row, 1)

.

You would then replace the date criteria in your COUNTIFS with looking at this new column and which boxes are checked.

Luis Perez · 11/11/21

Does this work without the helper column yet?

COUNTIFS formula that compares dates in two columns as a criteria

Comments

Help Article Resources

Categories