Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Countif Tasks are completed late?

Johnny Rox
edited 12/09/19 in Archived 2016 Posts

Hey There~

I'm trying to count how many of my tasks are being completed late and I'm struggling to do this.


Here's what I got:


Here is the formula:
=COUNTIF([Completion Dates]:[Completion Dates], >[Due Dates]:[Due Dates])


This returns zero but I want it to return three. However, =COUNTIF([Completion Dates]1, >[Due Dates]1) returns one.


I've tried variations of @cell but still no dice.




Screen Shot 2016-11-14 at 11.39.35 AM.png



  • Johnny Rox
    edited 11/14/16

    Hey Andrew,


    That's true, but I have a pretty large sheet and I'm trying to reduce the number of assisting columns.



  • Alternative solution:


    Create a checkbox column where to identify if a row's Completion Date > Due Date. Then, count that column to get your result.


    =IF([Completion Dates]1 > [Due Dates]1, 1) 

    =COUNTIF([Past Due]:[Past Due], 1)

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭



    I'm pretty sure this (an additional column) is not one you can avoid.


    The COUNTIF or COUNTIFS functions allow ONE cell reference, not a matching one is another column of the same size.

    There is no OFFSET function, so even trying to use the @cell reference for one column to the right, for example, won't work either. I (like you) tried various things with @cell and came up empty.

    There is no way to "store" a value in the formula and use it later in the same formula.


    All of the usual suspects are dead ends.At least for now.




This discussion has been closed.