Countifs Criterion referencing other columns

I am trying to count data based on a completion date being after the request date (data error checking).

"=COUNTIFS(Completed:Completed, >Requested@cell)"

This give me and #Unparseable error


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Eric Nelson

    I see you used @cell as the row reference in this formula. Try using @row instead! This tells the formula to look at the cell in the "Requested" column for this current row, or @ this row.

    =COUNTIFS(Completed:Completed, > Requested@row)

    See: Create Efficient Formulas with @cell and @row



  • =COUNTIFS(Completed:Completed,

    >Requested:Requested) doesn't work

    =COUNTIFS(Completed:Completed, >Requested@row) doesn't work either

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 11/22/22

    Hi @Eric Nelson

    The second formula has the correct syntax:

    =COUNTIFS(Completed:Completed, >Requested@row)

    When you say it doesn't work, are you receiving an error or an incorrect result? This formula assumes that your "Completed" column is in the current sheet and is set to a Date format. It's then looking to count how many rows have a date that's in the future compared to the date in the one cell of this row, in the Requested column. Is that what you're looking to do?

    If not, it would be helpful to see screen captures of your sheet set-up, but please block out sensitive data.



  • I am doing this in a sheet summary (should have said that before). It works fine in a column in the sheet.

    =SUMIFS(Completed:Completed, >[VWRS Requested]@row), both Completed and VWRS Requested are date values.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @Eric Nelson

    Where is the "Requested" cell located? Is it in the Sheet Summary as well, or is it in the Grid of the sheet? This changes how the formula should be written.

    For example, in a sheet summary you reference the field with a #, like so:

    =SUMIFS(Completed:Completed, >[VWRS Requested]#)

    If it's in the sheet, you'll need to add the row number itself after the column reference, like so:

    =SUMIFS(Completed:Completed, >[VWRS Requested]1)

    ^That's for row one. If your value is in row 5, you would use:

    =SUMIFS(Completed:Completed, >[VWRS Requested]5)

    Does that make sense? See: Create a Cell or Column Reference in a Formula

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!