Counting a range (not in adjacent columns) and then conditional format

[buyshirts]

So i have a table and i want to conditionally format the whole row if there are 2 negative numbers out of 3 selected columns and these Columns are not adjacent.

If the columns were next to each other i thought i could use something along the lines of

=IF(COUNTIF(A1:C1,"<0") < 2 but if i try to select A1,B1,C1 im getting an error

Any help is much appreciated

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what is the range & which columns should be checked.

 

[buyshirts]

Sorry about that, poor from me!.

So i have worked out how to SUM a non contiguous range using INDIRECT with the following forumla but i assume i need a TRUE or FALSE to use with Conditional formatting?

 

[Fluff]

Thanks for updating your profile, but can you answer my other question?

 

[buyshirts]

Sorry again!

So the range is Range is G2:O2 and the Columns to be used are G2,K2,O2

I got to =SUM(COUNTIF(INDIRECT({"G2","K2","O2"}),"<0")) but that is returning me the Vaule 2

 

[Fluff]

Thanks for that, how about

=SUMPRODUCT(($G2:$O2<0)*(MOD(COLUMN($G2:$O2),4)=3))>1

 

[buyshirts]

2022-23.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB														AP	AQ	AR	AS
1	Week		2022/23	Running Total			2021/22	Running Total			2020/21	Running Total			2019/20	Running Total			2018/19	Running Total			2017/18	Running Total		Month	2016/17	Running Total														Total		Weekly Average
2	1		-8846.06	-8846.06			258.27	258.27			-1512.83	-1512.83			-2429.59	-2429.59			1598.10	1598.10			1117.59	1117.59		Aug	4247.22	4247.22														4928.21		616.03
3	2		0.00	-8846.06			7762.42	8020.69			4836.65	3323.82			5086.01	2656.42			46.68	1644.78			3277.59	4395.18		Aug	1241.88	5489.10														7724.58		965.57
4	3						2971.74	10992.43			3226.32	6550.14			3366.94	6023.36			1219.48	2864.26			2612.96	7008.14			4876.20	10365.30														22494.06		2811.76
5	4				-8846.06		5835.70	16828.13	16828.13		-1769.91	4780.23	4780.23		6045.33	12068.69	12068.69		1516.55	4380.81	4380.81		6715.88	13724.02	13724.02		-4204.44	6160.86														13643.37		1705.42
6	5						3644.59	20472.72			7051.21	11831.44			2711.92	14780.61			3223.79	7604.60			439.46	14163.48			-226.20	5934.66														12061.44		1507.68
7	6						-5853.70	14619.02			5976.38	17807.82			3849.26	18629.87			6639.86	14244.46			3724.50	17887.98			1359.97	7294.63														14916.10		1864.51
8	7						575.05	15194.07			-4896.39	12911.43			180.79	18810.66			8769.91	23014.37			9758.43	27646.41			4137.34	11431.97														17849.81		2231.23
9	8						5182.60	20376.67	3548.54		1540.82	14452.25	9672.02		5381.43	24192.09	12123.40		5308.75	28323.12	23942.31		3058.38	30704.79	16980.77		2053.40	13485.37														8550.08		1068.76
10	9						-2442.80	17933.87			-19247.17	-4794.92			1820.45	26012.54			8035.85	36358.97			3323.88	34028.67			-506.40	12978.97														-2101.83		-262.73
11	10						3081.72	21015.59			575.15	-4219.77			3325.87	29338.41			2136.59	38495.56			2027.45	36056.12			4660.87	17639.84														14049.64		1756.21
12	11						-11077.89	9937.70			3741.82	-477.95			-11572.20	17766.21			5945.93	44441.49			1635.24	37691.36			410.32	18050.16														915.64		114.46
13	12						9810.13	19747.83	-628.84		7178.46	6700.51	-7751.74		1472.54	19238.75	-4953.34		3464.42	47905.91	19582.79		210.93	37902.29	7197.50		116.98	18167.14														12910.34		1613.79
Comparison

Cell Formulas
Range	Formula
C2:C3	C2	=OFFSET('2022'!$D$10,(ROW('2022'!D1)-1)*7,0,1,1)
D2,L2,H2	D2	=C2
D3,L3:L13,H3:H13	D3	=IFERROR(SUM(C3+D2),"")
G2:G13	G2	=OFFSET('2021'!$D$10,(ROW('2021'!D1)-1)*7,0,1,1)
K2:K13	K2	=OFFSET('2020'!$D$10,(ROW('2020'!D1)-1)*7,0,1,1)
O2:O13	O2	=OFFSET('2019'!$D$10,(ROW('2019'!D1)-1)*7,0,1,1)
P2,AB2,X2,T2	P2	=SUM(O2)
P3:P13,AB3:AB13,X3:X13,T3:T13	P3	=SUM(O3+P2)
S2:S13	S2	=OFFSET('2018'!$D$10,(ROW('2018'!D1)-1)*7,0,1,1)
W2:W13	W2	=OFFSET('2017'!$D$10,(ROW('2017'!D1)-1)*7,0,1,1)
AA2:AA13	AA2	=OFFSET('2016'!$D$10,(ROW('2016'!D1)-1)*7,0,1,1)
E5,U13,Q13,M13,I13,E13,U9,Q9,M9,I9,E9,U5,Q5,M5,I5	E5	=IF(COUNT(C2:C5),SUM(C2:C5),"")
Y5,Y13,Y9	Y5	=SUM(W2:W5)
AP2:AP13	AP2	=SUM(K2,O2,S2,W2,AA2,AE2,AI2,AM2)
AR2:AR13	AR2	=AVERAGE(K2,O2,S2,W2,AA2,AE2,AI2,AM2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:AR53	Expression	=SUMPRODUCT((G2:O2<0)*(MOD(COLUMN(G2:O2),4)=3))>1	text	NO
A2:AR53	Expression	=AND($AR2<0,$AV2>2)	text	NO
A2:AR53	Expression	=AND($AR2<0,$AV2>1)	text	NO
A2:AR53	Expression	=$AV2>2	text	NO

So hopefully i have attached a mini sheet screen shot
What my aim is, is to highlight a whole row which corresponds to week of the year the weekly totals are in Column G-K-O-S-W-AA but i only want to use G-K-O to determine whether the criteria is met.
If you look at Row 2 it starts of with a Blue fill which is correct but then it goes into orange.

 

[Fluff]

You haven't locked the columns ranges as I showed.

 

[buyshirts]

Sorry i must of made a mistake when i put in in as the whole table was blue!

Thanks very much for your help, it would be so much easier if i read your posts properly!

 

[Fluff]

Glad it's sorted & thanks for the feedback.