Formula Needed

Options

Hi, I am creating a formula for more than one row in the same column.

The current formula I am using is:

=IF([On Track/ Behind]21 = "On Track", "On Track", "Behind")

The above formula only works for row 21.

I still need to include On/Behind row numbers' 33,45,57,69,81,93,105

When using =IF([On Track/ Behind]21:[On Track/ Behind]105 = "On Track", "On Track", "Behind")

I get the error of "Invailid Operation.

Any assistance would be great!

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @MorganElias

    Can you explain a little further what it is you're looking to do?

    Paul's example where @row is used is helpful if you're looking into one row, this current row, to see if it's "on track" or "behind". However, from your explanation about the other rows, it seems like you may be looking to COUNT how many times each of these values appear, across your whole sheet (or specific rows in your sheet).

    How are you selecting the specific rows to look in to (33,45,57,69,81,93,105), is there any indication on these rows that we can use to create a "filter" in the formula (ex. are they Parent rows or do they have a checked box on them)?

    You can use a COUNTIF function to search the entire column, and if any one of those cells says "Behind" you can make it say "Behind", like so:

    =IF(COUNTIF([On Track/ Behind]:[On Track/ Behind], "Behind") >= 1, "Behind", "On Track")


    If I've misunderstood your set-up and what you're looking to achieve, it would be helpful to see screen captures but please block out sensitive data.

    Cheers,

    Genevieve

  • MorganElias
    MorganElias ✭✭
    Answer ✓
    Options

    Hi @Genevieve P.,

    I would like to have the following "Light Blue" roles reflect if an employee is on track or behind for their orientation process.

    The following formula: =IF(COUNTIF([On Track/ Behind]:[On Track/ Behind], "Behind") >= 1, "Behind", "On Track") was helpful.

    Thank you for the clarification and assistance.

Answers

  • Paul H
    Paul H ✭✭✭✭✭✭
    Options

    Try

    =IF([On Track/ Behind]@row= "On Track", "On Track", "Behind")

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @MorganElias

    Can you explain a little further what it is you're looking to do?

    Paul's example where @row is used is helpful if you're looking into one row, this current row, to see if it's "on track" or "behind". However, from your explanation about the other rows, it seems like you may be looking to COUNT how many times each of these values appear, across your whole sheet (or specific rows in your sheet).

    How are you selecting the specific rows to look in to (33,45,57,69,81,93,105), is there any indication on these rows that we can use to create a "filter" in the formula (ex. are they Parent rows or do they have a checked box on them)?

    You can use a COUNTIF function to search the entire column, and if any one of those cells says "Behind" you can make it say "Behind", like so:

    =IF(COUNTIF([On Track/ Behind]:[On Track/ Behind], "Behind") >= 1, "Behind", "On Track")


    If I've misunderstood your set-up and what you're looking to achieve, it would be helpful to see screen captures but please block out sensitive data.

    Cheers,

    Genevieve

  • MorganElias
    MorganElias ✭✭
    Answer ✓
    Options

    Hi @Genevieve P.,

    I would like to have the following "Light Blue" roles reflect if an employee is on track or behind for their orientation process.

    The following formula: =IF(COUNTIF([On Track/ Behind]:[On Track/ Behind], "Behind") >= 1, "Behind", "On Track") was helpful.

    Thank you for the clarification and assistance.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!