Fórmula genérica
=SUMPRODUCT(data*(headers=A1))
Resumo
Para somar valores em colunas combinando cabeçalhos de coluna, você pode usar uma fórmula baseada na função SUMPRODUCT. No exemplo mostrado, a fórmula em J5 é:
=SUMPRODUCT(data*(LEFT(headers)=J4))
onde "dados" é o intervalo nomeado B5: G14 e "cabeçalhos" é o intervalo nomeado B4: G4.
A fórmula soma as colunas em que os cabeçalhos começam com "a" e retorna 201.
Explicação
Basicamente, esta fórmula se baseia na função SUMPRODUCT para somar valores em colunas correspondentes no intervalo nomeado "dados" C5: G14. Se todos os dados fossem fornecidos para SUMPRODUCT em um único intervalo, o resultado seria a soma de todos os valores no intervalo:
=SUMPRODUCT(data) // all data, returns 387
Para aplicar um filtro combinando cabeçalhos de coluna - colunas com cabeçalhos que começam com "A" - usamos a função ESQUERDA desta forma:
LEFT(headers)=J4) // must begin with "a"
Esta expressão retorna TRUE se o cabeçalho de uma coluna começar com "a" e FALSE se não começar. O resultado é uma matriz:
(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)
Você pode ver que os valores 1,2 e 5 correspondem às colunas que começam com "a".
Dentro de SUMPRODUCT, esse array é multiplicado por "dados". Devido à transmissão, o resultado é uma matriz bidimensional como esta:
(8,10,0,0,7,0;9,10,0,0,10,0;8,6,0,0,6,0;7,6,0,0,6,0;8,6,0,0,6,0;10,11,0,0,7,0;7,8,0,0,8,0;2,3,0,0,3,0;3,4,0,0,4,0;7,7,0,0,4,0)
Se visualizarmos esse array em uma tabela, é fácil ver que apenas os valores nas colunas que começam com "a" sobreviveram à operação, todas as outras colunas são zero. Em outras palavras, o filtro mantém os valores de interesse e "cancela" o restante:
| A001 | A002 | B001 | B002 | A003 | B003 |
|---|---|---|---|---|---|
| 8 | 10 | 0 | 0 | 7 | 0 |
| 9 | 10 | 0 | 0 | 10 | 0 |
| 8 | 6 | 0 | 0 | 6 | 0 |
| 7 | 6 | 0 | 0 | 6 | 0 |
| 8 | 6 | 0 | 0 | 6 | 0 |
| 10 | 11 | 0 | 0 | 7 | 0 |
| 7 | 8 | 0 | 0 | 8 | 0 |
| 2 | 3 | 0 | 0 | 3 | 0 |
| 3 | 4 | 0 | 0 | 4 | 0 |
| 7 | 7 | 0 | 0 | 4 | 0 |
Com apenas uma única matriz para processar, SUMPRODUCT retorna a soma de todos os valores, 201.
Soma por correspondência exata
O exemplo acima mostra como somar colunas que começam com um ou mais caracteres específicos. Para somar a coluna com base em uma correspondência exata, você pode usar uma fórmula mais simples como esta:
=SUMPRODUCT(data*(headers=J4))
