Using CONTAINS or LEFT in a COUNTIFS

Jade Boring

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.

Colleen Patterson

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)

Colleen Patterson

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)

Jade Boring

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)))

Colleen Patterson

@Jade Boring Glad we were able to get this fixed up for you!