Fórmula genérica
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Resumo
Para pesquisar um valor em várias planilhas em uma pasta de trabalho e retornar uma contagem, você pode usar uma fórmula baseada nas funções CONT.SE e INDIRETO. Com alguma configuração preliminar, você pode usar essa abordagem para pesquisar um valor específico em uma pasta de trabalho inteira. No exemplo mostrado, a fórmula em C5 é:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Contexto - dados de amostra
A pasta de trabalho contém 4 planilhas no total. Folha1 , Folha2 e Folha3, cada uma contém 1000 nomes aleatórios que se parecem com isto:
Explicação
O intervalo B7: B9 contém os nomes das planilhas que queremos incluir na pesquisa. Essas são apenas cadeias de texto e precisamos fazer algum trabalho para que sejam reconhecidas como referências de planilhas válidas.
Trabalhando de dentro para fora, esta expressão é usada para construir uma referência de folha completa:
"'"&B7&"'!"&"1:1048576"
As aspas simples são adicionadas para permitir nomes de planilhas com espaços, e o ponto de exclamação é uma sintaxe padrão para intervalos que incluem um nome de planilha. O texto "1: 1048576" é um intervalo que inclui todas as linhas da planilha.
Depois que B7 é avaliado e os valores são concatenados, a expressão acima retorna:
"'Sheet1'!1:1048576"
que vai para a função INDIRETO como o argumento 'ref_text'. INDIRETO avalia este texto e retorna uma referência padrão para cada célula na Planilha1 . Isso vai para a função CONT.SE como o intervalo. Os critérios são fornecidos como uma referência absoluta para C4 (bloqueado para que a fórmula possa ser copiada para a coluna C).
CONT.SE então retorna uma contagem de todas as células com um valor igual a "maria", 25 neste caso.
Nota: COUNTIF não faz distinção entre maiúsculas e minúsculas.
Contém x igual
Se você deseja contar todas as células que contêm o valor em C4, em vez de todas as células iguais a C4, você pode adicionar curingas aos critérios como este:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Agora, CONT.SE contará células com a substring "John" em qualquer lugar da célula.
atuação
Em geral, não é uma boa prática especificar um intervalo que inclua todas as células da planilha. Isso pode causar problemas de desempenho, uma vez que o intervalo inclui milhões e milhões de células. Neste exemplo, o problema é agravado, pois a fórmula usa a função INDIRETA, que é uma função volátil. As funções voláteis são recalculadas a cada alteração da planilha, portanto, o impacto no desempenho pode ser enorme.
Quando possível, restrinja os intervalos a um tamanho razoável. Por exemplo, se você sabe que os dados não aparecerão após a linha 1000, você pode pesquisar apenas as primeiras 1000 linhas desta forma:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
