Formula Needed
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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
-
Try
=IF([On Track/ Behind]@row= "On Track", "On Track", "Behind")
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!