Countifs Criterion referencing other columns

Eric Nelson
Eric Nelson ✭✭
edited 11/21/22 in Formulas and Functions

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

Answers

  • 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

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • =COUNTIFS(Completed:Completed,

    >Requested:Requested) doesn't work

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


  • Genevieve P.
    Genevieve P. Employee
    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.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • 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.

  • 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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!