SOLVED
Home

Coloring rows in Excel based on how many criteria are met

Highlighted
rhuen
Occasional Contributor

I have just gotten into working with the developer tab of Excel, and I need some help. I am trying to find an easier way to color rows on a sheet based on the number of criteria that is met. I could technically do this with Conditional Formatting. I actually started to do it before deciding how ridiculous it would be:

 

ALL

=AND(OR($D2>55, $G2<=1979),$E2="Male",OR($F2="Single",$F2="Widowed"),$L2>5,$M2>100)

 

MISSING ONE

=OR(AND(OR($D2>55, $G2<=1979),$E2="Male",OR($F2="Single",$F2="Widowed"),$L2>5), AND(OR($D2>55, $G2<=1979),$E2="Male",OR($F2="Single",$F2="Widowed"),$M2>100), AND(OR($D2>55, $G2<=1979),$E2="Male",$L2>5,$M2>100), AND(OR($D2>55, $G2<=1979), OR($F2="Single",$F2="Widowed"),$L2>5,$M2>100), AND($E2="Male",OR($F2="Single",$F2="Widowed"),$L2>5,$M2>100))

 

Don't bother looking into that too much. I'll explain the criteria: The perfect constituent is older than 55 OR graduated before 1979, is male, is widowed OR single, has five gifts or more, and has given $100 or more. If they meet the maximum of five of these, the row is highlighted orange. If they meet four, it's blue And so on~

 

I will attach an example workbook of what the final product should look like and a plain worksheet I am trying to apply it to. Please ask questions if you have any. Thank you in advance!

2 Replies
Highlighted
Solution

Hi, 

that could be like

=((OR(INDIRECT("Table1[@AGE]")>55,INDIRECT("Table1[@CLASS]")<1979))+(INDIRECT("Table1[@GENDER]")="Male")+(OR(INDIRECT("Table1[@STATUS]")="Widowed",INDIRECT("Table1[@STATUS]")="Single"))+(INDIRECT("Table1[@['# OF GIFTS]]")>=5)+(INDIRECT("Table1[@[TOTAL GIFT AMOUNT]]")>=100))=5

for first rule, etc

 

Highlighted

Perfect, thank you!

Related Conversations
Creating Pie charts.......
dougler2020 in Excel on
0 Replies
How to use the trendline to find percentage change?
fishfish31 in Excel on
0 Replies
tableau
Noemie911 in Excel on
4 Replies
Summarzing data
Ravi_Kumar in Excel on
1 Replies