Fórmula genérica
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Resumo
Para contar as linhas visíveis apenas com critérios, você pode usar uma fórmula bastante complexa baseada em SUMPRODUCT, SUBTOTAL e OFFSET. No exemplo mostrado, a fórmula em C12 é:
=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))
Prefácio
A função SUBTOTAL pode facilmente gerar somas e contagens para linhas ocultas e não ocultas. No entanto, ele não é capaz de lidar com critérios como CONT.SE ou SOMASE sem alguma ajuda. Uma solução é usar SUMPRODUCT para aplicar a função SUBTOTAL (via OFFSET) e os critérios. Os detalhes dessa abordagem são descritos a seguir.
Explicação
Basicamente, essa fórmula funciona configurando dois arrays dentro do SUMPRODUCT. A primeira matriz aplica critérios e a segunda matriz lida com a visibilidade:
=SUMPRODUCT(criteria*visibility)
Os critérios são aplicados com parte da fórmula:
=(C5:C8=C10)
O que gera uma matriz como esta:
(FALSE;TRUE;FALSE;TRUE)
Onde TRUE significa "atende aos critérios". Observe que, como estamos usando multiplicação (*) nesta matriz, os valores TRUE FALSE serão automaticamente convertidos em 1's e 0's pela operação matemática, então terminamos com:
(0;1;0;1)
O filtro de visibilidade é aplicado usando SUBTOTAL, com a função número 103.
SUBTOTAL é capaz de excluir linhas ocultas ao executar cálculos, então podemos usá-lo neste caso para gerar um "filtro" para excluir linhas ocultas dentro de SUMPRODUCT. O problema, porém, é que SUBTOTAL retorna um único número, enquanto precisamos de uma matriz de resultados para usá-lo com sucesso dentro de SUMPRODUCT. O truque é usar OFFSET para alimentar SUBTOTAL uma referência por linha, de modo que OFFSET retorne um resultado por linha.
Claro, isso requer outro truque, que é dar a OFFSET um array que contém um número por linha, começando com zero. Fazemos isso com uma expressão construída na função ROW:
=ROW(C5:C8)-MIN(ROW(C5:C8)
que irá gerar uma matriz como esta:
(0;1;2;3)
Em resumo, a segunda matriz (que lida com a visibilidade usando SUBTOTAL), é gerada assim:
=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0)) =SUBTOTAL(103,OFFSET(C5,(0;1;2;3),0)) =SUBTOTAL(103,("East";"West";"Midwest";"West")) =(1;0;1;1)
E, finalmente, temos:
=SUMPRODUCT((0,1,0,1)*(1;0;1;1))
Que retorna 1.
Critérios múltiplos
Você pode estender a fórmula para lidar com vários critérios como este:
=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))
Somando os resultados
Para retornar uma soma de valores em vez de uma contagem, você pode adaptar a fórmula para incluir um intervalo de soma:
=SUMPRODUCT(criteria*visibility*sumrange)
Os critérios e matrizes de visibilidade funcionam da mesma forma explicada acima, excluindo células que não são visíveis. Se precisar de correspondência parcial, você pode construir uma expressão usando ISNUMBER + SEARCH, conforme explicado aqui.
