There are more sophisticated approaches that would use the list function with a table of related records and the ≠ operator, but just staying with your original expression, you could use this expression:
Substitute ( List ( If ( color_1 = "red" ; "" ; color_1 ) ; If ( color_2 = "red" ; "" ; color_2 ) ; If ( color_3 = "red" ; "" ; color_3 ) ; If ( color_4 = "red" ; "" ; color_4 ) ) ; ¶ ; ", " )
The List function turns this into a list of values separated by returns. The substitute function replaces the returns with a comma and a space.
This gives me an error that says "In the function Average, Count, Extend, GetRepetition, Max, Min, NPV, StDev, Sum, GetSummary or GetNthRecord, an expression was found where a field alone is needed."
If you have a better solution that my calculation by all means I would prefer it.
Still looking for an answer. Thanks.
NM, Idid something incorrect in the copy process. It worked. Thank you very much, and sorry for the incorrect reply.