Fórmula genérica
=SUM(INDEX(FILTER(range,logic),SEQUENCE(n,1,1,1)))
Resumo
Para somar os primeiros n valores correspondentes em um conjunto de dados, você pode usar uma fórmula baseada nas funções FILTRO e SEQUÊNCIA. No exemplo mostrado, a fórmula na célula G5, copiada, é:
=SUM(INDEX(FILTER(score,name=F5),SEQUENCE(3,1,1,1)))
onde nome (B5: B16) e pontuação (C5: C16) são intervalos nomeados.
Explicação
A função FILTER, nova no Excel 365, pode ajudar a simplificar alguns problemas complicados de fórmulas.
Neste exemplo, o objetivo é somar as 3 primeiras pontuações de Jake e Hailey, com base na ordem em que aparecem na tabela. Há 12 pontuações no total, e Jake e Hailey têm 6 pontuações cada.
Trabalhando de dentro para fora, a primeira tarefa é gerar uma lista de pontuações para cada nome. Isso é feito com a função FILTER:
FILTER(score,name=F5)
Com "Jake" na célula F5, o resultado é uma matriz com todas as pontuações de Jake como esta:
(6;5;7;7;6;8)
Esta matriz é retornada à função INDEX como o argumento da matriz:
INDEX((6;5;7;7;6;8),SEQUENCE(3,1,1,1))
A função SEQUENCE é usada para gerar o valor para o número da linha e retorna uma matriz com 3 números,
SEQUENCE(3,1,1,1) // returns (1;2;3)
Neste ponto, podemos escrever a parte INDEX da fórmula assim:
INDEX((6;5;7;7;6;8),(1;2;3))
INDEX retorna valores associados às 3 primeiras linhas da matriz para a função SUM:
=SUM((6;5;7)) // returns 18
e SUM retorna a soma desses valores como o resultado final em G5. Quando a fórmula é copiada para a célula G6, o resultado é a soma das três primeiras pontuações de Hailey.
Soma dos últimos n valores correspondentes
Para somar os últimos n valores correspondentes, você pode adaptar a fórmula assim:
=SUM(INDEX(FILTER(score,name=F5),SEQUENCE(3,1,SUM(--(name=F5)),-1)))
Esta fórmula é explicada com mais detalhes aqui.
