Search code examples
excelvbacolorsconditional-statementshighlighting

Excel conditional formatting count color

I've been searching, and finding, information regarding conditional formatting and counting the colors. However, I have not been able to figure out how to do what I want.

In text: I fetch data from the internet which I add in a 15 row x 20 column matrix (in 8 different sheets). I then want to highlight the top three values green and bottom three red per column and finally count all green/red per row. Basically like this;

screenshot

(Eventhough I'm using values 1-10 in the picture above, it can be anything. It's the top/bottom three that's of interest.)

Problem with what I've found on counting colors that are conditionally formatted is that the conditions are to simple (i.e above value x, between x and y, and so on), so I cannot find a way to do this (ish). I haven't tried it yet, but I guess I can evaluate a formula and conditionally formatting the cell based on the result of the evaluation. However, it seems then that I would need a formula per cell to do this which isn't something I want to do... Example (based on screen shot):

=AND(NOT(ISBLANK(A3));A3<=SMALL(A$3:A$12;3))

and then:

=AND(NOT(ISBLANK(A4));A4<=SMALL(A$3:A$12;3))

all the way to:

=AND(NOT(ISBLANK(F12));F12<=SMALL(F$3:F$12;3))

Any ideas? Thanks in advance!

Some links I've checked (could only add one...): http://www.cpearson.com/excel/CFColors.htm

Solution 
  • =IF(LARGE(A$1:A$15;COUNTIF(A$1:A$15;MAX(A$1:A$15))+2)<=A1;1;0)

    That's the formula if the first cell of your datas is at cell A1.

    Copy the formula into an empty cell with enough space to extend it in a 15*20 matrix.

    Then sum up all the rows and you will have you desired result ;)

    You can hide the columns used to calculate the final result if you want !

    To do the same for smallest values you just have to use SMALL instead of LARGE, and change the criteria of the COUNTIF :

    =IF(SMALL(A$1:A$15;COUNTIF(A$1:A$15;MIN(A$1:A$15))+2)>=A1;1;0)

    You can also use those formulas as an expression for your conditional formatting

    Edit : screenshot of how it should look like

    g