Fórmula genérica
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Resumo
Para somar valores em colunas e linhas correspondentes, você pode usar a função SUMPRODUCT. No exemplo mostrado, a fórmula em J6 é:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
onde dados (C5: G14), dias (B5: B14) e códigos (C4: G4) são intervalos nomeados.
Explicação
A função SUMPRODUCT pode manipular matrizes nativamente, sem exigir a entrada de deslocamento de controle.
Nesse caso, estamos multiplicando todos os valores nos dados do intervalo nomeado por duas expressões que filtram valores não de interesse. A primeira expressão aplica um filtro baseado em códigos:
(codes=J4)
Como J4 contém "A002", a expressão cria uma matriz de valores TRUE FALSE como este:
(FALSE,TRUE,FALSE,FALSE,FALSE)
A segunda expressão filtra no dia:
(days=J5)
Como J4 contém "Quarta", a expressão cria uma matriz de valores TRUE FALSE como este:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
No Excel, os valores TRUE FALSE são forçados automaticamente para valores 1 e 0 por qualquer operação matemática, então a operação de multiplicação coage os arrays acima para uns e zeros e cria um array 2D com as mesmas dimensões dos dados originais. O processo pode ser visualizado conforme mostrado abaixo:
Por fim, SUMPRODUCT retorna a soma de todos os elementos na matriz final, 9.
Contar em vez de somar
Se quiser contar os valores correspondentes em vez de somar, você pode encurtar a fórmula para:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Observe que essa contagem incluirá células vazias.
Notas
- Embora o exemplo mostre apenas uma coluna correspondida, esta fórmula somará corretamente várias colunas correspondidas.
- Se você só precisa combinar colunas (não linhas), pode usar uma fórmula como esta.
- Para corresponder apenas as linhas, você pode usar a função COUNTIFS.
