# Formula to count Conditional Formats

#### midsession

##### Board Regular
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?

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### midsession

##### Board Regular
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

##### MrExcel MVP, Moderator
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.

#### Peter_SSs

##### MrExcel MVP, Moderator
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

##### Board Regular
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

##### Board Regular
Attempt v0.3.xls
ABCDEFGHIJKLMNOPQRSTUVW
1Percentile90A
226.66701.40.8670.61.8605050.216056.312.55091.758.3316.733.370.831
36.673.031.131.070.671.3353.3333.339.7096.36-31-8125.0058.33100.0025.000.00-10.531
400
500001011011000000005
600001010001110100006
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

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

#### Peter_SSs

##### MrExcel MVP, Moderator
Is this too tricky?
Is this impossible?
Should I go back to trying COUNTIF?
Sorry, I'm not seeing any easy way forward here.

Replies
1
Views
113
Replies
24
Views
412
Replies
10
Views
195
Replies
1
Views
384
Replies
14
Views
224

1,181,897
Messages
5,932,686
Members
436,851
Latest member
masthomas

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back