Count a conditionally formatted color

[Tom Repden]

Hello all, new member and 2nd post / question now!
My full sheet consists of approx 800 rows of customers and approx 90 columns of the various products
You have helped me use CF to highlight my MAX and 2nd LARGE(est) values per row.

Now my goal is to COUNT how many of those two values are in each (product) column.
I have found how to add a colorfunction module that will count by color but will not work when the color originates from conditional formatting.

So I need to COUNT cells that are <<color>> from a CF .

If this is a wrong direction and you have other solutions to achieve my needs, awesome!

thx again
Tom
Barrie, Ontario Canada
Beautiful warm & sunny day here at 1° C !!!

 

[SirJB7]

Hi, Tom Repden!

Whether your function ColorFunction works fine (which I didn't checked) I'm afraid I'm bad news: cells colored via CF conditions still keep their original Interior.Color value, it's just a matter of which one gets displayed by any suited CF. So you can't retrieve the CF back color unless you replicate the CF condition and do a lot of stuff around it.

If I were you I'd face the problem via SUMPRODUCT of COUNTIFS functions, replicating in part the finding of highest and next higher values.

Regards!

 

[Tom Repden]

ahhh :-( too bad but also very good so I can learn now about those solutions! thank you! t

 

[SirJB7]

Hi, Tom Repden!

Yes, color handling from Excel formulas has been always very obfuscated, since I remember we all pray that in next version MS will come up with something friendly, maybe for 2050 version...

About SUMPRODUCT/COUNTIFS suggestion, do you think you can manage to handle them? Give it a try, or a couple of tries, or a whole rugby match, and if you don't succeed tell us and surely someone might be able to help you.

Regards!

 

[Tom Repden]

thx SirJB7 - I've done some reading on both functions; so am looking forward to the challenge of the game. May tag you again if the bruises on my head from hitting the wall start to hurt too much! Cheers t

 

[Hui]

Tom
Chandoo.org has lots of posts on Countif/s and Sumproduct to assist you in learning about these functions

 

[NARAYANK991]

Hi Tom ,

This is from a very old post ; see if it helps.

It works for single column ranges , where the CF is through a formula , and where the values are numeric.

Narayan

 

[amp_82]

This is sort of cheating, and not sure if useful in your case, but if i need to keep the colours without the formatting i copy and paste the table in to word, then back in to excel. I tried this on your sample file and the count formula you have seems be counting the cells with colour,though it isn't differentiating the two colours.

 

[Gayani]

Having Same Issue mentioned above