Ir para o conteúdo principal

Como contar ou somar células com base nas cores da fonte em Excel?

Como você poderia obter o número de células ou somar todas as células que contêm uma cor de fonte específica em Excel? Por exemplo, tenho um intervalo de dados em uma planilha, conforme mostrado a seguir na captura de tela, e agora quero contar ou somar, respectivamente, as células que possuem uma cor de fonte vermelha, azul e preta. Normalmente não existe uma maneira direta de lidar com essa tarefa, aqui falarei sobre alguns truques para resolver esse trabalho.

Contar ou somar células com base nas cores da fonte com função definida pelo usuário

Conte ou some células com base na cor da fonte com algumas funções úteis

Conte ou some células com base na cor da fonte com um recurso incrível - Contagem por cor


Contar ou somar células com base nas cores da fonte com função definida pelo usuário

Contar células com base nas cores da fonte:

Para calcular o número de células com cores de fonte específicas, faça o seguinte:

1. Segure o ALT + F11 chaves, e abre o Janela Microsoft Visual Basic for Applications.

2. Clique inserção > Móduloe cole o seguinte código no Janela Módulo.

Código VBA: conte células com base nas cores da fonte:

Public Function CountColour(pRange1 As Range, pRange2 As Range) As Double
'Update by Extendoffice
Application.Volatile
Dim rng As Range
For Each rng In pRange1
    If rng.Font.Color = pRange2.Font.Color Then
        CountColour = CountColour + 1
    End If
Next
End Function

3. Em seguida, salve este código e volte para a planilha, e então insira esta fórmula em uma célula em branco = CountColour (A1: D10, A2) , veja a captura de tela:

Anote os: Na fórmula acima, A1: D10 é o intervalo que você deseja usar e A2 é a célula com uma cor de fonte específica que você deseja contar.

4. Depois de digitar a fórmula, pressione Entrar , e você obterá o número de células com cores de fonte vermelhas. Se você quiser contar outras células com cores de fonte, insira a fórmula repetidamente, conforme necessário. Você obterá os seguintes resultados:


Soma de células com base nas cores da fonte:

Para somar células com base nas cores da fonte, a seguinte Função Definida pelo Usuário pode ajudá-lo.

1. Segure o ALT + F11 chaves, e abre o Janela Microsoft Visual Basic for Applications.

2. Clique inserção > Móduloe cole o seguinte código no Janela Módulo.

Código VBA: células de soma com base nas cores da fonte:

Public Function SumByColor(pRange1 As Range, pRange2 As Range) As Double
'Update by Extendoffice
Application.Volatile
Dim rng As Range
Dim xTotal As Double
xTotal = 0
For Each rng In pRange1
    If rng.Font.Color = pRange2.Font.Color Then
        xTotal = xTotal + rng.Value
    End If
Next
SumByColor = xTotal
End Function

3. Em seguida, salve este código e volte para a planilha original e, a seguir, insira esta fórmula = SumByColor (A1: D8, A1) em uma célula em branco, veja a captura de tela:

Anote os: Na fórmula acima, A1: D10 é o intervalo que você deseja usar e A2 é a célula com uma cor de fonte específica que você deseja somar.

4. Então aperte Entrar , e você adicionará todas as células com cores de fonte vermelhas. Se você deseja somar outras células com cores de fonte, insira a fórmula repetidamente. Você obterá o seguinte resultado:


Conte ou some células com base na cor da fonte com algumas funções úteis

Pode ser que a Função Definida pelo Usuário seja problemática para você salvar e aplicar, aqui, vou recomendar uma ferramenta útilKutools for Excel, com suas funções avançadas, você pode resolver esta tarefa de forma rápida e fácil.

Dicas:Para aplicar isso CONTAGEM POR FONTE COR e SUMBYFONTCOLOR recursos, em primeiro lugar, você deve baixar o Kutools for Excele, em seguida, aplique o recurso de forma rápida e fácil.

Depois de instalar Kutools for Excel, por favor, faça o seguinte:

Contar células com base nas cores da fonte:

1. Clique na célula onde deseja colocar o resultado da contagem e clique em Kutools > Kutools Funções > Estatística e Matemática > CONTAGEM POR FONTE COR, veja a captura de tela:

2. No Argumentos de função caixa de diálogo, especifique o intervalo de dados e a célula de índice de cor que você deseja contar pela cor da fonte, consulte a captura de tela:

3. E então, clique OK botão, você obterá o primeiro resultado da contagem, para obter outros resultados, você só precisa copiar esta fórmula e alterar as referências de células conforme sua necessidade. Veja a imagem:


Soma de células com base nas cores da fonte:

1. Clique na célula onde deseja colocar o resultado da contagem e clique em Kutools > Kutools Funções > Estatística e Matemática > SUMBYFONTCOLOR, veja a captura de tela:

2. No Argumentos de função caixa de diálogo, especifique o intervalo de dados e a célula de índice de cor que você deseja somar pela cor da fonte, consulte a captura de tela:

3. E então, clique OK botão, você obterá o resultado da primeira soma, para obter outros resultados, você só precisa copiar esta fórmula e alterar as referências de células conforme sua necessidade. Veja a imagem:

Clique para fazer o download Kutools for Excel e teste grátis agora!


Conte ou some células com base na cor da fonte com um recurso incrível - Contagem por cor

Kutools for Excel também oferece um recurso fácil Contagem por Cor, com este utilitário, você pode obter rapidamente o resultado do cálculo, como contagem, soma, células médias e assim por diante, pela cor de fundo, cor da fonte, formatação condicional conforme necessário.

Dicas:Para aplicar isso Contagem por Cor recurso, em primeiro lugar, você deve baixar o Kutools for Excele, em seguida, aplique o recurso de forma rápida e fácil.

Depois de instalar Kutools for Excel, por favor, faça o seguinte:

1. Selecione o intervalo de dados que deseja contar e somar com base em cores diferentes.

2. Clique Kutools MAIS> Contagem por Cor, veja a captura de tela:

3. No Contagem por Cor caixa de diálogo, escolha Formatação padrão do Método de cor puxe para baixo e selecione fonte sob a Tipo de contagem suspenso, e as células com as mesmas cores de fonte foram contadas, somadas, calculadas a média e assim por diante, veja a captura de tela:

4. E, em seguida, clique em Gerar relatório botão, você obterá as estatísticas em uma nova pasta de trabalho. Veja a imagem:

Clique para fazer o download Kutools for Excel e teste grátis agora!


Mais artigos:

  • Contar e somar células com base na cor de fundo Excel
  • Suponha que você tenha um intervalo de células com diferentes cores de fundo, como vermelho, verde, azul e assim por diante, mas agora você precisa contar quantas células nesse intervalo têm uma determinada cor de fundo e somar as células coloridas com a mesma cor . Em Excel, não existe uma fórmula direta para calcular a soma e a contagem de células coloridas, aqui apresentarei algumas maneiras de resolver esse problema.
  • Somar / contar números em negrito em um intervalo de células Excel
  • Quando você tem um intervalo de dados que contém alguns números em negrito em uma planilha e agora deseja somar ou contar apenas as células em negrito, é claro que você pode adicioná-los um por um manualmente, mas isso consumirá muito tempo. Como você poderia somar ou contar apenas as células em negrito Excel de uma forma fácil e rápida?
  • Aplicar gradiente de cor em várias células
  • In Excel, podemos facilmente preencher a cor de fundo em uma célula ou em várias células, mas, às vezes, precisamos que a cor seja preenchida com gradiente, conforme mostrado a seguir, como podemos obter o gradiente de cor em uma célula ou em várias células em Excel?
  • Concatene colunas de células e mantenha a cor do texto Excel
  • Como todos sabemos, ao concatenar ou combinar colunas de células em uma coluna, a formatação da célula (como cor da fonte do texto, formatação de números, etc.) será perdida. Neste artigo, apresentarei alguns truques para combinar as colunas das células em uma e manter a cor do texto da maneira mais fácil possível. Excel.

Conte ou some células com base na fonte / plano de fundo / cor de formatação condicional:

Kutools for Excel: com mais de 300 úteis Excel suplementos, gratuitos para teste sem limitação em 30 dias. Baixe e teste grátis agora!

Melhores ferramentas de produtividade de escritório

Suporta Escritório/Excel 2007-2021 e 365 | Disponível em 44 idiomas | Fácil de desinstalar completamente

Recursos mais comuns: Localizar/destacar/identificar duplicatas   |  Excluir linhas em branco   |  Combine colunas ou células sem perder dados   |   Rodada sem Fórmula ...
Super pesquisa: VLookup de múltiplos critérios    VLookup de múltiplos valores  |   VLookup em várias planilhas   |   Pesquisa Difusa ....
Lista suspensa avançada: Crie rapidamente uma lista suspensa   |  Lista suspensa de dependentes   |  Lista suspensa de seleção múltipla ....
Gerenciador de colunas: Adicione um número específico de colunas     Mover colunas   |   Exibir colunas   |   Comparar colunas com Selecionar células iguais e diferentes ...
Recursos em destaque: Foco da Grade   |  Vista de Design   |   Grande Barra de Fórmula    Gerenciador de pastas de trabalho e planilhas   |  Biblioteca (Auto texto)   |  Data Picker   |  Combinar planilhas   |  Criptografar/Descriptografar Células    Enviar e-mails por lista   |  Super Filtro   |   Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens, ...)   |   50+ de cores Tipos (Gráfico de Gantt, ...)   |   Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário, ...)   |   19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho, ...)   |   12 Conversão Ferramentas (Números para Words, Conversão de moedas, ...)   |   7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células, ...)   |   ... e mais

Kutools for Excel Possui mais de 300 recursos, Garantindo que o que você precisa está a apenas um clique de distância...

Sobrecarregar seu Excel Habilidades: Experimente eficiência como nunca antes com Kutools for Excel  (Avaliação gratuita completa de 30 dias)

guia kte 201905

Garantia incondicional de devolução do dinheiro em 60 diasLeia mais... Download grátis... Compra... 

Office Tab Traz a interface com guias para o Office e torna seu trabalho muito mais fácil

  • Ative a edição e leitura com guias em Word, Excel, Power Point, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse todos os dias! (Avaliação gratuita completa de 30 dias)
Garantia incondicional de devolução do dinheiro em 60 diasLeia mais... Download grátis... Compra... 
 
Comments (52)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi..


I used it to count and sum from matrix. The problem is that the I need to count/sum in multiple range of cells. Is it possible to update this code to do this?
This comment was minimized by the moderator on the site
Hello, Joseph,

Maybe there is no direct way for you to count or sum the cell values based on font color in multiple ranges, but, you can apply the third method in this article and use Count by Color feature of Kutools for Excel, with this feature, you just need to select the multiple ranges first, and then apply the feature.
https://www.extendoffice.com/documents/excel/1418-excel-count-sum-by-font-color.html#a3

Please try it, thank you!
This comment was minimized by the moderator on the site
COUNT CELLS BY FONT COLOR tutorial was great! But it is not working when the font color was based on the conditional formatting. Do you have something for this concern?
This comment was minimized by the moderator on the site
Hello,
The codes in this article can not support the conditional formatting, but, you can use our Kutools for Excel's Count by Color feature, it can help you to count or sum conditional font colors. Please try it.
This comment was minimized by the moderator on the site
This was exactly what I needed - Thanks!
This comment was minimized by the moderator on the site
= SumByColor (A1: D8, A1) NÃO FUNCIONOU


=SumByColor(A1:D8;A1) FUNCIONOU

TIRANDO ESPAÇO E USANDO " ; " AO INVÉS DE " , " AI FUNCIONOU LEGAL.

USO EXCEL 10


MUITO BOM.


OBRIGADO
This comment was minimized by the moderator on the site
Is there a way to change Count Or Sum Cells Based On Font Colors With User Defined Function into count or sum cells based on de conditional formating?

I've tried with

Public Function CountColour(pRange1 As Range, pRange2 As Range) As Double

Application.Volatile
Dim rng As Range
For Each rng In pRange1

If rng.FormatConditions.Font.Color = pRange2.FormatConditions.Font.Color Then
CountColour = CountColour + 1

End If
Next
End Function

But it appears not to work, any suggestion?
Thanks
This comment was minimized by the moderator on the site
Hello, Borja,
May be you can use our product, Kutools for Excel, with its Count by Color feature, you can quickly count or sum the cell values based on the conditional formatting without any VBA code, please try. You can download it and free trial in 60 day.
Thank you!
This comment was minimized by the moderator on the site
I tried the SumByColor. It works, but not if the numbers have been coloured by conditional formatting. For example, I have a list of different numbers that are coloured red if they are within a range set by a conditional formatting rule. I would then like to sum only the red-coloured numbers. But the SumByColor VBA code does not work in this situation. Any suggestions to make it work. Thanks
This comment was minimized by the moderator on the site
Hello, Xiahui,
The above code can not applied to conditional formatting color, but, you can use our Kutools for Excel' Count by color feature, with it, you can quickly get the result for counting or summing based on the conditional formatting color. See the below screenshot:
This comment was minimized by the moderator on the site
Hey
Nice macro. I used it to just count from a simple matrix. The problem is that the macro counts also empty cells. How to exclude empty cells from counting?
This comment was minimized by the moderator on the site
Hello, Kriss,
To count the cells based on font color excluding the blank cells, please apply the below User Defined Function, please try:

Public Function CountColour(pRange1 As Range, pRange2 As Range) As Double
Application.Volatile
Dim rng As Range
For Each rng In pRange1
If rng.Value <> "" Then
If rng.Font.Color = pRange2.Font.Color Then
CountColour = CountColour + 1
End If
End If
Next
End Function
This comment was minimized by the moderator on the site
Exactly what I was looking for! This will save me lots of time. Thank You
This comment was minimized by the moderator on the site
Muito obrigada já ajudou muito, porém a minha formula só adiciona à soma outro número quando eu uso o pincel para mudar a cor, se eu trocar a cor da fonte pela barra de ferramenta não dá certo, alguém saber me explicar -
This comment was minimized by the moderator on the site
This was SO helpful - Thanks very much!
This comment was minimized by the moderator on the site
Hướng dẫn bên trên hình như chỉ áp dụng khi màu của giá trị trong ô hoặc màu nền của ô được tô màu bằng Manual.M muốn đếm số ô mà giá trị được bôi màu theo cách Conditional Formatting.Xin giúp đỡ ạ!
This comment was minimized by the moderator on the site
Hi I tried it but when I changed the color of other items, it doesnt add up or the sum doesnt change.
Please help me.
This comment was minimized by the moderator on the site
Hi,

I followed the instructions and it works well. But then when i re-open the file, the module window is empty. It seems i cannot save the code as needed. A box pops up saying that "VB project cannot be saved in macro-free workbooks" and therefore i have to save as a macro-free workbook.

Please advise if I have missed any steps.

Thanks,
J. Wong
This comment was minimized by the moderator on the site
Rather than count or sum cells based on font colour, is there a way to display certain 'values' in another column based on font colour? for example, I have a column with multiple company names that each have a specific font colour to indicate a specific calendar year. I would like to simply create a 'year' column beside this. So for example if column B contains text with font colour green, this will display "2017" in column C. And if column B contains text with font colour red, this will display "2016" in column C. I dread to do this manually as I have thousands of rows!
This comment was minimized by the moderator on the site
Hi,
I followed the instructions above for SumByColor and they worked great.
I was wondering whether there is a similar formula in VB to sum by background color rather than using Kutools.
thanks, Antonella
This comment was minimized by the moderator on the site
Hi, Antonella,
The VBA code in the following article may help you, please view it.
https://www.extendoffice.com/documents/excel/1155-excel-count-sum-cells-by-color.html

Hope it can help you!
Thank you!
This comment was minimized by the moderator on the site
hi, your solution works well but when i change the content of a cell in the range to the colour selected the result cell doesn't auto update unless i go to the result cell select the formula and hit enter. Is there a way to auto update the result cell when the range is amended.
This comment was minimized by the moderator on the site
Hello, Mike,
May be there is no other good method to update the result automatically when you change the cell font color, so, you need selec t the formula cell and hit Enter key or click the Automatic under the Formulas tab to get the updated result.
If you have any other good ways, please comment here.
Thank you very much!
This comment was minimized by the moderator on the site
I did everything as instructed by I get #VALUE! instead of a total. I changed the formula to subtotal one single cell and it worked but when the formula has a range of cells (in my case D3:D53, I get the #VALUE result.
This comment was minimized by the moderator on the site
I can't seem to get past this syntax error that I am getting. I'm not very knowledgeable when it comes to visual basic, so I'm hoping that it is just a small typing error (although I did cut and paste the code from above, rather than retype). The machine is running Windows 10/Office 2013. The error says "compile error / syntax error". The VB windows shows the line with the IF statement in the SumByColor module as highlighted. Please tell me what I'm doing wrong.
This comment was minimized by the moderator on the site
I followed the instructions for SUMbyColor (for font).Showed up perfectly on my screen (my work pc has Excel 2010 ) however, on my boss's screen it comes up as '#NAME?' (fyi, his pc has the current Excel version).Is there any way to resolve this?
This comment was minimized by the moderator on the site
[quote]I followed the instructions for SUMbyColor (for font).Showed up perfectly on my screen (my work pc has Excel 2010 ) however, on my boss's screen it comes up as '#NAME?' (fyi, his pc has the current Excel version).Is there any way to resolve this?By Liz[/quote] Hello,LIZ: After inserting the code, you should save the workbook as Excel Macro-Enabled Workbook file format. And then when you open this Excel file next time, you should click Enable Content from the security warning ribbon to activate the macro code. Please try this.
This comment was minimized by the moderator on the site
Hi tks for the advice. I have a similar problem of MYTAWALBEH I would like to include more columns in the function would be great if someone has some tips cheers
This comment was minimized by the moderator on the site
Great read and working brilliant however is it possible for anyone to explain how I could apply this to a multiple set of sheets?? Any idea will be much appreciated so cheers in advance.
This comment was minimized by the moderator on the site
Great read and works awesome but could someone please tell me how to apply this to multiple sheets. If anyone can, this will be brilliant for what I need and will be much appreciated indeed.
This comment was minimized by the moderator on the site
Is there any way to count the cells by colour without Kutools?
This comment was minimized by the moderator on the site
Hi! Works great, thanks. However, doesnt work for cells with more than one colour. Is it possible to update this code to do this? Nikki
This comment was minimized by the moderator on the site
How do I get the spread sheet to automatically update? If my data/color changes the sum doesn't update until I select the cell with the formula, hit F2, and then click off the cell. I want my sheet to automatically update as the data changes. Thanks.
This comment was minimized by the moderator on the site
Sir#name error aa rahi hai
This comment was minimized by the moderator on the site
Hi I need to count the number of cells in orange font from cell range B:B if Cell Range AB says 'Ventura' The above allows you to count according to one criteria but doesn't allow a second - I have tried but I have never written macros before and cannot get this working. Urgent help needed please? Thank you Leanne
This comment was minimized by the moderator on the site
I followed your directions for the count but in the cells that I have =CountColour(...) I have the #NAME error. I have multiple tabs, so I put the code in the sheet where I want to use it but I still got the error, so then I put it in a module. What am I missing??
This comment was minimized by the moderator on the site
Click the Microsoft Office Button , and then click Excel Options. Click Trust Center, click Trust Center Settings, and then click Macro Settings. Click the options to enable macro
This comment was minimized by the moderator on the site
thank you very much... needed a little debugging but I think that's because nothing is ever cookie cutter, what I did to fix my issues may be different than what anyone else may need to do (based on version, etc.) but once I took out the rem'd line and made sure that I was pointing to the right fields and format field to do the comparison to, it worked like a charm.
This comment was minimized by the moderator on the site
the countcolor function includes the spaces between the cell that has that specific color. can you fix this? thanks alot!
This comment was minimized by the moderator on the site
MYTawalbeh, I had the same issue. You have to save the workbook as a macro enabled workbook so the macro copies over. Otherwise you will continue to have the error because the macro isn't being copied over.
This comment was minimized by the moderator on the site
IT worked ,, thank you But when I save and close worksheet then re-open, it shows (#Name?) I've saved it (Macro-enabled)
This comment was minimized by the moderator on the site
Click to enable macros on the yellow bar at the top of the page when you open your document and your formulas will come back. Hope this helps.
This comment was minimized by the moderator on the site
Excellent work! Saved my wife's night! Thx
This comment was minimized by the moderator on the site
Hello, I keep getting an error as follows "Ambiguous name detected: SumByColor". What am i doing wrong?
This comment was minimized by the moderator on the site
I got this message when I tried to apply the same thing to another spreadsheet. When you go to insert module and copy in the text change it to SumByColour (different spelling and note there are two instances of it in the text). Then when I tried to use the formula in my second spreadsheet the new spelling came up which I selected and now the function works in this spreadsheet too. I hope this works for you because I am finding it extremely useful for various spreadsheets I work with.
This comment was minimized by the moderator on the site
I use a very similar method but have been playing with MULTI Colored fonts... say [Only the BOLD word is red], fingers crossed you have a thought as to how I may achieve this! Cheers Eddie
This comment was minimized by the moderator on the site
super cool, helped me a ton. if you don't mind me asking. when i try this on a colored cell number affected with "Conditional Formatting" it seems to see all the numbers as a single color. are there any way to have the program recognize that the color is different.
This comment was minimized by the moderator on the site
it works very good and so nice function. thank you.
This comment was minimized by the moderator on the site
How do we do well to access?
This comment was minimized by the moderator on the site
Hi, when i use this formula it keeps coming up with "Compile Error: Must be first statement on the line" and it has highlighted the "Function" on the first line of the formula. Help??
This comment was minimized by the moderator on the site
May be you did not insert a module instead copied the code on the screen after Alt+F11
This comment was minimized by the moderator on the site
Thank you so much! It works like wonder!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations