COUNTIFS formula that compares dates in two columns as a criteria

Steve Fogel

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

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.