Using CONTAINS or LEFT in a COUNTIFS

I am trying to create a helper column to flag when there are not enough safety cones in a vehicle depending on the vehicle type. For example Pickups need 6 minimum, Vans 8min and Buckets 12min. The types vary with PICKUP/FORD, PICKUP/CHEV, Pickup - Service Body, and Pickup. My first thought was to do a contains in the countifs formula, but that gave an UNPARSEABLE error.
=COUNTIFS([Safety - Cones]@row, <6, [Vehicle Type]@row, CONTAINS(Pickup, [Vehicle Type]@row))
Then I tried using a left formula.
=COUNTIFS([Safety - Cones]@row, <6, [Vehicle Type]@row, LEFT([Vehicle Type]@row, 6) = "Pickup")
Both conditions are true, but its still showing zero.
Best Answer
-
The formula you are working off currently would give you a count of the rows that meet that criteria, which does not read to me what you are hoping to achieve.
If you are looking to use a flag field, you would be better served to update your formula to something like this: =IF(AND([Safety - Cones]@row < 6, CONTAINS("Pickup", [Vehicle Type]@row)), 1, 0)
If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.
Always happy to work through questions or concerns!
Answers
-
The formula you are working off currently would give you a count of the rows that meet that criteria, which does not read to me what you are hoping to achieve.
If you are looking to use a flag field, you would be better served to update your formula to something like this: =IF(AND([Safety - Cones]@row < 6, CONTAINS("Pickup", [Vehicle Type]@row)), 1, 0)
If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.
Always happy to work through questions or concerns!
-
That worked. Thanks! The final version with all the requirements ended up being this formula:
=IF(AND([Safety - Cones]@row < 6, CONTAINS("Pickup", [Vehicle Type]@row)), 1, IF(AND([Safety - Cones]@row < 8, CONTAINS("Van", [Vehicle Type]@row)), 1, IF(AND([Safety - Cones]@row < 12, CONTAINS("Bucket", [Vehicle Type]@row)), 1)))
-
@Jade Boring Glad we were able to get this fixed up for you!
If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.
Always happy to work through questions or concerns!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!