Formula to count Conditional Formats

midsession

Board Regular
Joined
Oct 12, 2006
Messages
70
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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

midsession

Board Regular
Joined
Oct 12, 2006
Messages
70
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
Joined
May 28, 2005
Messages
49,431
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
49,431
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 12, 2006
Messages
70
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
Joined
Oct 12, 2006
Messages
70

ADVERTISEMENT

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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,431
Office Version
  1. 365
Platform
  1. Windows
Is this too tricky?
Is this impossible?
Should I go back to trying COUNTIF?
Sorry, I'm not seeing any easy way forward here. :cry:
 

Forum statistics

Threads
1,136,430
Messages
5,675,803
Members
419,586
Latest member
RoteichA

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top