• 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

    Hui...

  • 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

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


74350
 

Attachments

  • Somar celulas com nome e cor igual (BY).xlsm
    13.4 KB · Views: 5
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.
 
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
 
bosco_yip

Assim fica melhor calcula somente a F4:F21

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