Contar ocorrências de um texto específico em toda a pasta de trabalho do Excel

É fácil para nós contar quantas vezes um texto ou valor numérico específico ocorre em um intervalo de células com a função CONT.SE. No entanto, para contar ocorrências de um texto específico em toda a pasta de trabalho, uma fórmula mais complexa é necessária. Este artigo demonstrará uma fórmula baseada nas funções SOMARPRODUTO, CONT.SE e INDIRETO para resolver esse problema.

Como contar ocorrências em toda a pasta de trabalho?

Suponha que existam 4 planilhas (Planilha2, Planilha3, Planilha4 e Planilha5) na sua pasta de trabalho, e você deseja contar quantas vezes o texto específico “Laptop” aparece nessas 4 planilhas. Você pode fazer o seguinte para obter o resultado.

Fórmula Genérica

=SOMARPRODUTO(CONT.SE(INDIRETO("'"&intervalo1&"'!"&intervalo2);critério))

Argumentos

Como usar esta fórmula?

1. Primeiro, você precisará criar uma nova planilha e listar todos os nomes das planilhas em um intervalo de células. Neste caso, listei todos os nomes das planilhas no intervalo B7:B10.

2. Selecione uma célula em branco para exibir o resultado.

3. Insira uma das fórmulas abaixo nela e pressione a tecla Enter para obter o resultado.

Se você já tiver inserido o texto específico em uma célula (como B3), poderá referenciar essa célula na fórmula da seguinte forma:

=SOMARPRODUTO(CONT.SE(INDIRETO("'"&B7:B10&"'!A1:Z10000");B3))

Ou digite diretamente o texto específico entre aspas duplas na fórmula assim:

=SOMARPRODUTO(CONT.SE(INDIRETO("'"&B7:B10&"'!A1:Z10000");"Laptop"))

Nota: Aqui, o intervalo A1:Z10000 é arbitrário, você pode alterá-lo para se ajustar aos seus dados.

Como esta fórmula funciona?

=SOMARPRODUTO(CONT.SE(INDIRETO("'"&B7:B10&"'!A1:Z10000");B3))

• "'"&B7:B10&"'!A1:Z10000": Aqui, cada nome de planilha na matriz é concatenado ao intervalo A1:Z10000, resultando em uma matriz como {"'Planilha2'!A1:Z10000";"'Planilha3'!A1:Z10000";"'Planilha4'!A1:Z10000";"'Planilha5'!A1:Z10000"}.
• INDIRETO({"'Planilha2'!A1:Z10000";"'Planilha3'!A1:Z10000";"'Planilha4'!A1:Z10000";"'Planilha5'!A1:Z10000"}): A função INDIRETO referencia os intervalos nas quatro planilhas.
• CONT.SE(INDIRETO("'"&B7:B10&"'!A1:Z10000");B3): A função CONT.SE conta o número de vezes que o valor específico aparece em cada planilha e retorna uma matriz {13;13;13;13}. Isso significa que o texto específico aparece 13 vezes em cada planilha.
• SOMARPRODUTO({13;13;13;13}): A função SOMARPRODUTO soma todos os números na matriz e retorna o resultado final como 52.

Funções relacionadas

Fórmulas relacionadas