Fórmula genérica
=SUMPRODUCT(--(range1=criteria),range2)
Resumo
Para somar ou subtotais colunas com base em critérios em colunas adjacentes, você pode usar uma fórmula baseada na função SUMPRODUCT. No exemplo mostrado, a fórmula em J5 é:
=SUMPRODUCT(--($B5:$H5=J$4),$C5:$I5)
Explicação
Basicamente, essa fórmula usa SUMPRODUCT para multiplicar e depois somar os produtos de duas matrizes: matriz1 e matriz2 . O primeiro array, array1 , é configurado para atuar como um "filtro" para permitir apenas valores que atendam aos critérios.
Array1 usa um intervalo que começa na primeira coluna que contém valores que devem passar nos critérios. Esses "valores de critérios" ficam em uma coluna à esquerda e imediatamente adjacentes aos "valores de dados".
Os critérios são aplicados como um teste simples que cria uma matriz de valores TRUE e FALSE:
--($B5:$H5=J$4)
Este bit da fórmula "testa" cada valor no primeiro array usando os critérios fornecidos, então usa um duplo negativo (-) para forçar os valores TRUE e FALSE resultantes para 1's e 0's. O resultado é o seguinte:
(1,0,0,0,1,0,1)
Observe que 1s correspondem às colunas 1,5 e 7, que atendem aos critérios de "A".
Para array2 dentro de SUMPRODUCT, usamos um intervalo que é "deslocado" por uma coluna à direita. Este intervalo começa com a primeira coluna que contém os valores a serem somados e termina com a última coluna que contém os valores a serem somados.
Portanto, na fórmula de exemplo em J5, depois que as matrizes foram preenchidas, temos:
=SUMPRODUCT((1,0,0,0,1,0,1),(1,"B",1,"A",1,"A",1))
Uma vez que SUMPRODUCT é programado especificamente para ignorar os erros que resultam da multiplicação de valores de texto, a matriz final se parece com isto:
(1,0,0,0,1,0,1)
Os únicos valores que "sobrevivem" à multiplicação são aqueles que correspondem a 1s dentro de array1 . Você pode pensar na lógica em array1 "filtrando" os valores em array2 .
