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
-
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
-
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
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!