Supondo que tenhamos uma tabela de vendas de frutas conforme a imagem abaixo mostrada, iremos contar ou somar as células coloridas na coluna Quantidade. Nessa situação, podemos filtrar a coluna Quantidade por cor e, em seguida, contar ou somar as células coloridas filtradas pela função SUBTOTAL facilmente no Excel.

1. Selecione células em branco para entrar na função SUBTOTAL.

1. Para contar todas as células com a mesma cor de fundo, insira a fórmula = SUBTOTAL (102, E2: E20);
2. Para somar todas as células com a mesma cor de fundo, insira a fórmula = SUBTOTAL (109, E2: E20);

Anote os: Em ambas as fórmulas, E2: E20 é a coluna Quantidade que contém as células coloridas e você pode alterá-las conforme necessário.

2. Selecione o cabeçalho da tabela e clique em Data > filtros. Veja a imagem:

3. Clique no ícone Filtro  na célula de cabeçalho da coluna Valor e clique em Filtrar por Cor e a cor especificada pela qual você contará sucessivamente. Veja a imagem:

Após a filtragem, ambas as fórmulas SUBTOTAL contam e somam todas as células coloridas filtradas na coluna Quantidade automaticamente. Veja a imagem:

Anote os: Este método requer que as células coloridas que você contará ou somará estejam na mesma coluna.

Um clique para contar, somar e calcular a média das células coloridas no Excel

Com o excelente Contagem por Cor característica de Kutools for Excel, você pode contar, somar e calcular a média de células rapidamente por cor de preenchimento ou cor de fonte especificada com apenas um clique no Excel. Além disso, este recurso também descobrirá os valores máximos e mínimos das células pela cor de preenchimento ou cor da fonte.

Neste método, criaremos um intervalo nomeado com a função GET.CELL, obteremos o código de cores das células e então contaremos ou somaremos pelo código de cores facilmente no Excel. Faça o seguinte:

1. Clique Fórmulas > Definir o Nome. Veja a imagem:

2. Na caixa de diálogo Novo nome, siga as instruções abaixo na captura de tela:
(1) Digite um nome na caixa Nome;
(2) Insira a fórmula = GET.CELL (38, Folha4! $ E2) na caixa Refere-se a (nota: na fórmula, 38 significa retornar o código da célula, e Folha4! $ E2 é a primeira célula na coluna Amount, exceto o cabeçalho da coluna, que você precisa alterar com base nos dados da tabela.)
(3) Clique no OK botão.

3. Agora adicione uma nova coluna Color à direita da tabela original. Em seguida digite a fórmula = NumColor e arraste a alça de Preenchimento automático para aplicar a fórmula a outras células na coluna Cor. Veja a imagem:
Anote os: Na fórmula, NumColor é o intervalo nomeado que especificamos nas 2 primeiras etapas. Você precisa alterá-lo para o nome especificado definido.

Agora o código de cor de cada célula na coluna Amount retorna na Coluna Color. Veja a imagem:

4. Copie e liste a cor de preenchimento em um intervalo em branco na planilha ativa e digite as fórmulas ao lado dela, conforme a captura de tela abaixo:
A. Para contar células por cor, insira a fórmula = CONT.SE ($ F $ 2: $ F $ 20, NumColor);
B. Para somar células por cor, insira a fórmula = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

Anote os: Em ambas as fórmulas, $ F $ 2: $ F $ 20 é a coluna Color, NumColor é o intervalo nomeado especificado, $ E $ 2: $ E $ 20 é a coluna Valor e você pode alterá-los conforme necessário.

Agora você verá que as células na coluna Quantidade são contadas e somadas por suas cores de preenchimento.

Supondo que as células coloridas se espalhem em um intervalo conforme a captura de tela abaixo mostrada, os dois métodos acima não podem contar ou somar as células coloridas. Aqui, este método apresentará um VBA para resolver o problema.

1. Segure o ALT + F11 chaves, e abre o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Móduloe cole o código a seguir na janela do módulo.

VBA: conte e some células com base na cor de fundo:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

3. Em seguida, salve o código e aplique a seguinte fórmula:
A. Conte as células coloridas: = função de cor (A, B: C, FALSE)
B. Some as células coloridas: = função de cor (A, B: C, VERDADEIRO)

Nota: nas fórmulas acima, A é a célula com a cor de fundo específica que você deseja calcular a contagem e a soma, e B: C é o intervalo de células onde você deseja calcular a contagem e a soma.

4. Faça a captura de tela a seguir, por exemplo, insira a fórmula= função de cor (A1, A1: D11, FALSE) para contar as células amarelas. E use a fórmula = função de cor (A1, A1: D11, TRUE) para somar as células amarelas. Veja a imagem:

5. Se você quiser contar e somar outras células coloridas, repita a etapa 4. Em seguida, você obterá os seguintes resultados:

Kutools for Excel também suporta algumas funções úteis para ajudar os usuários do Excel a fazer cálculos especiais, como contagem por cor de fundo da célula, soma por cor da fonte, etc.

1. Selecione a célula em branco onde colocar os resultados da contagem e clique Kutools > Kutools Funções > Estatística e Matemática > CONT.PELA COR DA CÉLULA. Veja a imagem:

2. Na caixa de diálogo Argumentos da função, especifique o intervalo em que contará as células coloridas dentro do Referência caixa, escolha a célula que é preenchida com a cor de fundo especificada no Color_index_nr caixa, e clique no OK botão. Veja a imagem:

notas:
(1) Você também pode digitar o especificado Kutools função = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2)  na célula em branco ou barra de fórmula diretamente para obter os resultados da contagem;
(2) Clique Kutools > Kutools Funções > Estatística e Matemática > SUBYCELLCOLOR ou digite = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) na célula em branco diretamente para somar células com base na cor de fundo especificada.
Aplique o CONT.PELA COR DA CÉLULA e SUBYCELLCOLOR funções para cada cor de fundo separadamente e você obterá os resultados conforme a captura de tela abaixo:

Kutools Funções contêm uma série de funções integradas para ajudar os usuários do Excel a calcular facilmente, incluindo Contagem / soma / células visíveis médias, Contagem / soma por cor de célula, Contagem / soma por cor da fonte, Contar personagens, Contar por fonte em negrito, Etc. Faça um teste grátis!

Com a função definida pelo usuário acima, você precisa inserir a fórmula uma a uma; se houver muitas cores diferentes, esse método será tedioso e demorado. Mas se você tiver Kutools for Excel'S Contagem por Cor utilitário, você pode gerar rapidamente um relatório das células coloridas. Você não só pode contar e somar as células coloridas, mas também pode obter os valores médio, máximo e mínimo da faixa colorida.

1. Selecione o intervalo que deseja usar e clique em Kutools MAIS > Contagem por Cor, veja a captura de tela:

2. E no Contagem por Cor caixa de diálogo, faça como abaixo a imagem mostrada:
(1) Selecione Formatação padrão do Método de cor lista suspensa;
(2) Selecione BACKGROUND do Tipo de contagem lista suspensa.
(3) Clique no botão Gerar relatório.

Anote os: Para contar e somar células coloridas por cor de formatação condicional específica, selecione Formatação condicional do Método de cor lista suspensa na caixa de diálogo acima, ou selecione Formatação padrão e condicional na lista suspensa para contar todas as células preenchidas pela cor especificada.

Agora você obterá uma nova pasta de trabalho com as estatísticas. Veja a imagem:

A Contagem por Cor recurso calcula (contagem, soma, média, máximo, etc.) células por cor de fundo ou cor de fonte. Faça um teste grátis!

Como contar / somar células com base nas cores das fontes no Excel?