Conditional formatting based on two selections

[ZoeM]

Hi Community.

I have Text Box which I want to Conditional format based on the selected in field action.

The Action is to Select two values (text values) and I would like it to format the text in the text box Blue. I tired using the If function below and various combinations of it but it cant seem to recognize what I have in parentheses which tells me my syntax is wrong:

=if(Upcoming_Sales=('One Week','Two Week'),RGB(0,176,240),RGB(54,54,54))

The text box is showing the sales coming up for the next two weeks and I want the font color and box to conditionally change when selected to Blue.

Any and all help will be truly appreciated.

[sunny_talwar]

Try this

=If(Match(Upcoming_Sales, 'One Week','Two Week'), RGB(0,176,240), RGB(54,54,54))

[ZoeM]

Thanks Sunny, but unfortunately it didn't work. Though QVW did like the formula, it did not format the font as desired.

[ZoeM]

I did find this though and it works like a charm for two values:

If(Concat(DISTINCT Upcoming_Sales,',')='One Week,Two Week',RGB(0,176,240),RGB(54,54,54))

But when I try to add a third value 'Four Week', it does not seem to work.

Any ideas on how I can add a third value?

[sunny_talwar]

I believe the Concatenation is done in ascending order of the text... may be try this

If(Concat(DISTINCT Upcoming_Sales,',') = 'Four Week,One Week,Two Week',RGB(0,176,240),RGB(54,54,54))

[sunny_talwar · Accepted solution]

Better still would be to do this

If(

SubStringCount(Concat(DISTINCT Upcoming_Sales,','), 'One Week') = 1 and

SubStringCount(Concat(DISTINCT Upcoming_Sales,','), 'Two Week') = 1 and

SubStringCount(Concat(DISTINCT Upcoming_Sales,','), 'Four Week') = 1,

RGB(0,176,240), RGB(54,54,54))

[ZoeM]

As usual Sunny, you fail to disappoint

This works like a charm