Count cells in a column with the same name and color

[S P P]

Boa noite!
Como faço para montar esta fórmula

 

[AlanSidman]

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.

 

[S P P]

Segue em Anexo

 

[bosco_yip]

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

Then,

3] In C4, formula copied right to D4 and all copied down :

=IF($B4="","",CountColor)

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

 

[S P P]

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

 

[deciog]

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/h...-in-conditional-formatting.44848/#post-268007

Decio

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/h...-in-conditional-formatting.44848/#post-268007
Decio

 

[S P P]

bosco_yip

Assim fica melhor calcula somente a F4:F21

>>> use code - tag <<<

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'ActiveSheet.Calculate
Sheets("Em inglês").Range("F4:F21").Calculate
End Sub