Highlighting cells that meets 3 yes criteria, 2 criteria and 1 c

Resolved · Urgent Priority · Version 365

Thu 7 Jun 2018, 16:43 Delegate Dimple said...

Dimple has attended:
Excel Intermediate course

Highlighting cells that meets 3 yes criteria, 2 criteria and 1 c

Hi there,
i have a list of 2000 people.
Column A has yes/no fields, column B has yes/no fields, column C has yes/no fields and column D has the person that it relates to. we want Column D to be colour coded so depending on how many yes/nos in column A/B/C for that particular individual, it goes red/amber/green as appropriate. So, if there is 1 yes either in column A B or C, then column D goes green. If there are 2 yes's in either column A B or C, then column D goes amber etc
What would be the best way to do this? Sorry, hard to explain without being able to attach an example but hope you understand what I mean/

Fri 8 Jun 2018, 13:31 Trainer Jens said...

RE: highlighting cells that meets 3 yes criteria, 2 criteria and

Hi Dimple,

Thank you for the forum question.

You can do what you want by using the formulas below.

Select column D

Click Conditional Formatting -> New Rule -> Use a Formula to determine.....

=(A2="Yes")+(B2="Yes")+(C2="yes")=3 "Format Red"
=(A2="Yes")+(B2="Yes")+(C2="yes")=2 "Format Amber"
=(A2="Yes")+(B2="Yes")+(C2="yes")=1 "Format Green"

In my example the cell in my selection is D2. If you start from D5 you must change the cell references to A5, B5, C5.

I hope this makes sense.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector