Formula to count Conditional Formats

[midsession]

Hello MrExcel

I'm trying to count cells affected by conditional formatting and I need help tidying up my efforts so far.

The numeric values in my table are highlighted if they exceed values in corresponding columns on other tables. The highlighting can be changed by the user changing the table to compare against. The logic for each cell runs something like this:

if cell is numeric
if column in (E, M, X, Y)
if (value < condition1 or > ccondition2)
highlight in blue
end
else ' columns not in (E, M, X, Y)
if (value > condition1 or < condition2)
highlight in red
end
end
end

My attempt to put together a formula for each cell appears to work, it looks like this for cell B3:
'=AND(ISNUMBER($B3),
OR(
AND(
OR(COLUMN()=5,COLUMN()=13,COLUMN()=24,COLUMN()=25),
OR(B3<VLOOKUP($B$1, INDIRECT($A$1), COLUMN(B3), FALSE),
$B3>INDEX(INDIRECT($A$1), MATCH($B$1,INDIRECT($A$1 & "I"),0)+ 2,COLUMN(B3)))),
AND(
NOT(OR(COLUMN()=5,COLUMN()=13,COLUMN()=24,COLUMN()=25)),
OR($B3>VLOOKUP($B$1, INDIRECT($A$1), COLUMN($B3), FALSE),
$B3<INDEX(INDIRECT($A$1), MATCH($B$1,INDIRECT($A$1 & "I"),0)+ 2,COLUMN($B3))))))

I can total the boolean values thus generated using '=COUNTIF(B21:AB21,TRUE)

But I do need a formula that will count highlighted cells for each row in the table. (I reckon I need either SUMPRODUCT, or an array formula using COUNTIF or COUNT(IF).

Can anyone help to tidy this formula?

 

[midsession]

I've got a bit further but I still need help

I now have a formula for each cell in the row which appears to work for single cells only
=SUMPRODUCT(
--(ISNUMBER(B3)),
--(ISERROR(MATCH(COLUMN(),{5,13,24,25},0))),
(--(B3>VLOOKUP($B$1,INDIRECT($A$1),COLUMN(),FALSE)))
+(--(B3<INDEX(INDIRECT($A$1),MATCH($B$1,INDIRECT($A$1&"I"),0)+2,COLUMN()))))
+ SUMPRODUCT(
--(ISNUMBER(B3)),
--(ISNUMBER(MATCH(COLUMN(),{5,13,24,25},0))),
(--(B3<VLOOKUP($B$1,INDIRECT($A$1),COLUMN(),FALSE)))
+(--(B3>INDEX(INDIRECT($A$1),MATCH($B$1,INDIRECT($A$1&"I"),0)+2,COLUMN()))))

If you fill each corresponding cell with this formula then the SUM of the values is correct. If you then try to replace the value B3 with $B3:$AB3 (the size of the array in question), formula will return '#REF!'. You have to fill in the COLUMN reference with $B3:$AB3, whereupon function returns the wrong total answer
NB: function returns different results, but still wrong, if entered as an array function

Am I barking up the wrong tree trying it this way?

 

[Peter_SSs]

midsession

Any chance of posting a sample of your sheet using Colo's HTML maker? Here's how:
http://www.mrexcel.com/board2/viewtopic.php?t=92622
You would need to add direct colour to the cells coloured by Conditional Formatting as Conditional Formatting is not copied to the board by the HTMl maker. Then you might be able to explain your requirements clearly by reference to the sample posted.

 

[midsession]

[HTML removed by admin]

 

[Peter_SSs]

midsession

OK, getting closer with the HTML Maker, but a few comments:

1. I think you have tried to post too much of the sheet which is why it looks incomplete and why my post looks funny. When you are using the HTML Maker, take note if it tells you the code size is over the maximum and try again with a smaller sample.

2. When you get a good sample going, try to explain your requirements and relate them clearly to the sample.

3. As mentioned in my previous post: If you are posting a sample that has Conditional Formatting, you will have to colour the cells manually before using the HTML Maker if you want the colour to show on the board.

4. If you need to do some more testing with the HTML Maker there is a special 'Test Here' forum just for that sort of thing. Go to the Forum Index and look towards the bottom of the list.

 

[midsession]

Sorry Peter, I did ask the mod to get rid of that attempt. Cannot now get the addin to generate a html file for some reason - have asked for help in the html maker forum. Will try again soon

 

[midsession]

Attempt v0.3.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Percentile90	A
2		26.667	0	1.4	0.867	0.6	1.8	60	50	50.2	160	56.3	12.5	50	91.7	58.33	16.7	33.3	7	0.8		3	1
3		6.67	3.03	1.13	1.07	0.67	1.33	53.33	33.33	9.70	96.36	-31	-81	25.00	58.33	100.00	25.00	0.00	-1	0.5		3	1
4																						0	0
5		0	0	0	0	1	0	1	1	0	1	1	0	0	0	0	0	0	0	0		5
6		0	0	0	0	1	0	1	0	0	0	1	1	1	0	1	0	0	0	0		6
Sheet1

OK here's a cutdown version.
The user changes the highlighted values in rows 2 and 3 by changing the values in A1 and B1. These index a set of tables on another sheet, hence the VLOOKUP and INDEX. My stab at the sumproduct is in column V (normal) and W (array formula).
Rows 5 and 6 contain my earlier attempt to check the logic of each cell and sum the result.
Highlighted values are F2, H2, I2, K2, L2, F3, H3 (in red), L3, M3, N3, P3 (in blue)
Highlighted values

 

[midsession]

Is this too tricky?
Is this impossible?
Should I go back to trying COUNTIF?

 

[Peter_SSs]

Is this too tricky?
Is this impossible?
Should I go back to trying COUNTIF?

Sorry, I'm not seeing any easy way forward here.