Resumo
Para construir uma tabela de resumo bidirecional que some dados numéricos com mais de um critério, você pode usar a função SUMIFs. No exemplo mostrado, a fórmula em H5, copiada no intervalo H5: K7, é:
=SUMIFS(value,name,$G5,stage,H$4)
onde valor (C5: C15), nome (B5: B15) e estágio (D5: D15) são intervalos nomeados. O resultado é uma tabela que mostra os totais resumidos para cada nome por estágio.
Explicação
A função SUMIFS foi projetada para somar valores numéricos usando vários critérios.
No exemplo mostrado, os dados no intervalo B5: E15 mostram um pipeline de vendas onde cada linha é uma oportunidade pertencente a um vendedor, em um estágio específico. A fórmula em H5 é:
=SUMIFS(value,name,$G5,stage,H$4)
A primeira parte da fórmula soma as oportunidades por vendedor:
=SUMIFS(value,name,$G5 // sum by name
- Intervalo de soma são os valores de intervalo nomeados
- Intervalo de critérios 1 é o intervalo nomeado nome
- O critério 1 vem da célula G5
Observe que $ G5 é uma referência mista, com a coluna bloqueada e a linha relativa. Isso permite que a fórmula mude conforme necessário quando a fórmula é copiada em toda a tabela.
O próximo par de intervalo / critério em SUMIFS, somas por estágio:
stage,H$4 // sum by stage
- O intervalo de critérios 2 é o estágio de intervalo nomeado
- O critério 2 é H $ 4
Novamente, H $ 4 é uma referência mista, com a coluna relativa e a linha bloqueada. Isso permite que os critérios selecionem os valores do estágio na linha 4 conforme a fórmula é copiada na tabela.
Com os dois critérios juntos, a função SUMIFS soma corretamente as oportunidades por nome e por estágio.
Sem faixas de nomes
Este exemplo usa intervalos nomeados apenas por conveniência. Sem intervalos nomeados, a fórmula equivalente é:
=SUMIFS($C$5:$C$15,$B$5:$B$15,$G5,$D$5:$D$15,H$4)
Observe que as referências para nome, valor e estágio agora são referências absolutas para evitar alterações conforme a fórmula é copiada na tabela.
Observação: uma tabela dinâmica também seria uma excelente maneira de resolver esse problema.
