Excel for Mac (v16.40) - Conditional Formatting Formula

[TAPS_MikeDion]

Hi all,
I am assuming someone will see this and the formula will be a piece of cake, but I can't wrap my head around it. LOL

All I'm looking to do is to have a conditional formatting formula that checks column P for an x, and COUNTIF (or whatever would work best) columns W to AH (months of the year) if there is an x in each column.

1. If there is an x in column P, then the minimum number of x's from W to AH must be 7 in order for the conditional format BG color to change.
2. If there is no x in column P, then the minimum number of x's from W to AH must be 5 in order for the conditional format BG color to change.

Thank you to anyone offering to help on this!

 

[etaf]

2 Rules
=AND($P2="X",COUNTIF($W2:$AH2,"X")>=7)
and
=AND($P2<>"X",COUNTIF($W2:$AH2,"X")>=5)

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
P2:AH1000 - Change, reduce or extend the rows to meet your data range of rows

>Conditional Formatting
Manage Rules
+
Style = classic
use a formula
=AND($P2="X",COUNTIF($WS2:$AH2,"X")>=7)

Format [Number, Font, Border, Fill] - Whatever colour needed
choose the format you would like to apply when the condition is true
OK >> OK

Repeat for 2nd RULE

Book1
	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1
2
3
4	x							x	x	x	x	x	x	x
5								x	x	x	x	x
6
7
8	x
9
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P2:AC8	Expression	=AND($P2<>"X",COUNTIF($W2:$AH2,"X")>=5)	text	NO
P2:AC8	Expression	=AND($P2="X",COUNTIF($W2:$AH2,"X")>=7)	text	YES

 

[TAPS_MikeDion]

Thanks for the quick response etaf. I will see how that works.

It can't be done as one rule? That's what has been stumping me.

 

[etaf]

Yes , if you only want 1 colour

OR( AND($P2="X",COUNTIF($W2:$AH2,"X")>=7), AND($P2<>"X",COUNTIF($W2:$AH2,"X")>=5) )

I updated the 1st answer with an XL2BB example

 

[TAPS_MikeDion]

Sweeeeet! Thank you. I appreciate this!

 

[etaf]

Book1
	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1
2								x		x		x		x		x
3										x			x
4	x							x	x	x	x	x	x	x
5								x	x	x	x	x
6								x	x	x
7								x	x	x	x	x
8								x	x
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P2:AH8	Expression	=OR( AND($P2="X",COUNTIF($W2:$AH2,"X")>=7), AND($P2<>"X",COUNTIF($W2:$AH2,"X")>=5) )	text	YES

 

[TAPS_MikeDion]

Thank you very much etaf, this worked exactly how I needed it to work. I greatly appreciate your assistance on this!

 

[etaf]

you are welcome