• 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.

Count cells in a column with the same name and color


Well-Known Member
Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.


Excel Ninja
Formula solution in count cells with color

1] Copy criteria background color from "Name" to "Result" header : C3 with Red, D3 with Yellow

2] Create a range name by :
  • Select C4 >> Formulas >> Define Name >>
  • Name : Countcolor
  • Refers to : =SUMPRODUCT((GET.CELL(63,IF(1,+OFFSET($F$4:$F$21,ROW($F$4:$F$21)-MIN(ROW($F$4:$F$21)),)))=GET.CELL(63,C$3))*($F$4:$F$21=$B4))
  • Finish

3] In C4, formula copied right to D4 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




New Member
Bom Dia!
E deste modo que pretendo, porem quando mudo a cor da célula ou limpo a célula não muda o mesmo valor.
Usei ActiveSheet.Calculate in Worksheet_SelectionChange functionou

Obrigado pela ajuda.


Active Member
Em Português Brasil
SPP, Bom Dia.

Neste caso as formula são do Excel 4 e é preciso teclar F9 para obter o resultado ou usar a função que o colega @Peter fez neste link https://chandoo.org/forum/threads/how-to-count-the-color-cell-in-conditional-formatting.44848/#post-268007


In English
SPP, Good morning.

In this case the formulas are from Excel 4 and you need to press F9 to get the result or use the function that colleague @Peter did on this link https://chandoo.org/forum/threads/how-to-count-the-color-cell-in-conditional-formatting.44848/#post-268007


New Member

Assim fica melhor calcula somente a F4:F21

>>> use code - tag <<<
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("Em inglês").Range("F4:F21").Calculate
End Sub
Last edited by a moderator: