• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to count the color cell in conditional formatting?


Excel Ninja
Since you have value In B2 and B3, you could use it as criteria for the COUNTIF function and instead of count color cells.

1] In F7, copied down :


or, =COUNTIF($B7:$E7,">="&B$2)

2] In G7, copied down :


or, =COUNTIF($B7:$E7,"<="&B$3)

Last edited:
Dear Bosco,

Thank you for your excel function.

What if there is no value in B2 and B3, could I just count only for cell color, such as how many red colors and how many green colors?
Reason: the value might change based on their(boss) requirement.

Kindly advise with thanks.


Active Member
Hi @Falinaicare ,


See if is it okey VBA Solution.

Option Explicit
'Code created by Sumit Bansal from https://trumpexcel.com
Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
  If rCell.Interior.ColorIndex = CountColorValue Then
    TotalCount = TotalCount + 1
  End If
Next rCell
GetColorCount = TotalCount
End Function


Last edited:

Peter Bartholomew

Well-Known Member
It is just about possible to make Excel respond to manual changes of colour. I would not recommend it and prefer @bosco_yip's solution which uses the criterion that also controls the conditional format.
I defined a named function 'GET.COLOR' to be
which was invoked by the formula
in helper cells 'resultRange', leading to the formulae
= COUNTIFS(resultRange,"Red")
= COUNTIFS(resultRange,"Green")



Excel Ninja
Another Count Cell Color formula option using GetCell function but without helper cells.

1] Copy background color from data to criteria header : F6 with Red, G6 with Green

2] Create a range name by :
  1. Select F7 >> Formulas >> Define Name >>
  2. Name : Countcolor
  3. Refers to : =SUMPRODUCT(0+(GET.CELL(63,IF(1,+OFFSET($B7:$E7,,COLUMN($B7:$E7)-MIN(COLUMN($B7:$E7)))))=GET.CELL(63,F$6)))
  4. OK >> Finish

3] In F7, formula copied right to G7 and all copied down :


Remark : Since GetCell is a Macro 4 function, the file need to be saved in "xlsm" type "Excel Macro-Enabled Worksheet"



Last edited: