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.

Luis Perez

Does this work without the helper column yet?