CountIf Date formula compare different date columns

I have 3 date columns: Start Date, Draft Due Date, Final Due Date

The date columns are all initially auto-set to the same date; professionals are to subsequently update the Draft and Final due dates to a later date.

I would like to count the ones that never get updated, i.e. Draft Due Date = Start Date.

By the filters there is 20, however my summary formula shows zero with the following:

=COUNTIF([Draft Due Date]:[Draft Due Date], =[Start Date]:[Start Date])

I get #INVALID OPERATION with this formula:

=IF([Draft Due Date]:[Draft Due Date] = [Start Date]:[Start Date], 1, 0)

What will work?

Thank you!


  • Kelly P.
    Kelly P. ✭✭✭✭✭✭


    I'd put a backend helper checkbox column into the sheet (perhaps called "Dates Need Update") with the following column formula. The box will then check if either the draft due date or the final due date are the same as the start date.

    =IF(OR([Start Date]@row = [Draft Due Date]@row, [Start Date]@row = [Final Due Date]@row), 1, 0)

    In the sheet summary, I'd put the following formula:

    =COUNTIF([Dates Need Update]:[Dates Need Update], 1)

    Having the backend helper checkbox column in the sheet would also allow you to use conditional formatting to highlight the three date columns for any rows that have not been updated. In addition, you could use it to send update requests.

    Hope this helps!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!