How to count the color cell in conditional formatting?

[Falinaicare]

Dear Expert,

How to count in a row of red and green cell by using conditional formatting?

Excel attached.

Thanks.

 

[bosco_yip]

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 :

=COUNTIF($B7:$E7,">=200")

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

2] In G7, copied down :

=COUNTIF($B7:$E7,"<=199")

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

 

[Falinaicare]

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.

 

[rahulshewale1]

Hi @Falinaicare ,

https://trumpexcel.com/count-colored-cells-in-excel/

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

 

[Falinaicare]

Hi rahulshewale1

Thank you for your referral link. A good reference for me.

 

[rahulshewale1]

hi @Falinaicare ,

Welcome

 

[Falinaicare]

Hi ,

How to get automatic count as I have to double click the formula and enter then only get the answer a the cell =GetColorCount(B7:E7,$B$2)?

Hope to hear from you.

Thanks.

 

[Peter Bartholomew]

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
=CEILING.MATH(RAND(),1)*GET.CELL(63,testResult)
which was invoked by the formula
=LOOKUP(GET.COLOR,{0,3,43},{"","Red","Green"})
in helper cells 'resultRange', leading to the formulae
= COUNTIFS(resultRange,"Red")
= COUNTIFS(resultRange,"Green")

 

[Falinaicare]

Hi Peter Bartholomew,

I like your idea that combined with @bosco_yip excel code. Simple but only 1 question.

Where can I get the color code? {0,3,43}

=LOOKUP(GET.COLOR,{0,3,43},{"","Red","Green"})

Many Thanks

 

[bosco_yip]

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

Then,

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

=CountColor

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

 

[Peter Bartholomew]

The colour indices are from the original colour system offered in Excel; a pretty unpleasant set of colours they are too, with little apparent logic.
https://access-excel.tips/excel-vba-color-code-list/

BTW
CEILING.MATH(RAND(),1)
is intended to do nothing except force a re-evaluation whenever a change is made to the workbook.

 

[Falinaicare]

Thanks, bosco_yip, and Peter Bartholomew

Great learning from both of you.