Fórmula genérica
=SUM(INDEX(range,N(IF(1,(1,2,3)))))
Resumo
Para fazer INDEX retornar um array de itens para outra função, você pode usar um truque obscuro baseado nas funções IF e N. No exemplo mostrado, a fórmula em E5 é:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
onde "dados" é o intervalo nomeado B5: B10.
Explicação
É surpreendentemente complicado fazer com que INDEX retorne mais de um valor para outra função. Para ilustrar, a fórmula a seguir pode ser usada para retornar os três primeiros itens no intervalo nomeado "dados", quando inseridos como uma fórmula de matriz de várias células.
(=INDEX(data,(1,2,3)))
Os resultados podem ser vistos no intervalo D10: F10, que contém corretamente 10, 15 e 20.
No entanto, se envolvermos a fórmula na função SUM:
=SUM(INDEX(data,(1,2,3)))
O resultado final é 10, enquanto deveria ser 45, mesmo se inserido como uma fórmula de matriz. O problema é que INDEX retorna apenas o primeiro item na matriz para a função SUM. Para forçar INDEX a retornar vários itens para SUM, você pode envolver a constante de matriz nas funções N e IF assim:
=SUM(INDEX(data,N(IF(1,(1,2,3)))))
que retorna um resultado correto de 45. Da mesma forma, esta fórmula:
=SUM(INDEX(data,N(IF(1,(1,3,5)))))
retorna corretamente 60, a soma de 10, 20 e 30.
Essa técnica obscura às vezes é chamada de "desreferenciamento", porque impede que INDEX manipule os resultados como referências de célula e, subsequentemente, elimine todos, exceto o primeiro item na matriz. Em vez disso, INDEX fornece uma matriz completa de valores para SUM in. Jeff Weir tem uma boa explicação aqui sobre stackoverflow.
Nota para os leitores: não sei exatamente por que isso funciona. Se você puder fornecer uma explicação clara, irei atualizar para incluir.
