Formula not working

Options

Hello,

I have a formula that is working in one sheet but is in another.

What I am trying to achieve is to add a 1 into a column when a number is featured in another column only once so I can pull that into a report.

I am trying to pull a status report of contracts but the contracts are listed multiple times.

=IF(COUNTIF([Contract No #]$1:[Contract No #]$11254, [Contract No #]@row) <> 1, 0, 1)


Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Faye Sumner

    The range in your formula has a row start (row 1) and a row end (row 11254). If your first sheet doesn't have 11254 rows, then you'll receive an error, since it can't look that far down.

    =IF(COUNTIF([Contract No #]$1:[Contract No #]$11254, < here


    Can I ask why there are specific rows identified in your range? It looks like you just want to evaluate the whole column, is that correct? If so, you don't need any row reference at all!

    Try this:

    =IF(COUNTIF([Contract No #]:[Contract No #], [Contract No #]@row) <> 1, 0, 1)


    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!