Formula Needed

MorganElias

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!

Genevieve P.

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

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.

Paul H

Try

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

Genevieve P.

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

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.