Fórmula genérica
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Resumo
Para somar os primeiros n valores em um critério de correspondência de intervalo, você pode usar uma fórmula baseada na função LARGE, agrupada na função SUMPRODUCT. Na forma genérica da fórmula (acima), intervalo representa um intervalo de células que são comparadas aos critérios , valores representam valores numéricos dos quais os principais valores são recuperados e N representa a ideia do enésimo valor.
No exemplo, a célula ativa contém esta fórmula:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Onde cor é o intervalo nomeado B5: B12 e valor é o intervalo nomeado C5: C12.
Explicação
Em sua forma mais simples, LARGE retorna o "enésimo maior" valor em um intervalo com esta construção:
=LARGE (range,N)
Então, por exemplo:
=LARGE (C5:C12,2)
retornará o segundo maior valor no intervalo C5: C12, que é 12 no exemplo mostrado.
No entanto, se você fornecer uma "constante de matriz" (por exemplo, uma constante na forma (1,2,3)) para LARGE como o segundo argumento, LARGE retornará uma matriz de resultados em vez de um único resultado. Então, a fórmula:
=LARGE (C5:C12, (1,2,3))
retornará o 1º, 2º e 3º maiores valores C5: C12 em uma matriz como esta: (12,12,10)
Portanto, o truque aqui é filtrar os valores com base na cor antes que LARGE seja executado. Fazemos isso com a expressão:
(color=E5)
O que resulta em uma matriz de valores TRUE / FALSE. Durante a operação de multiplicação, esses valores são transformados em uns e zeros:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Portanto, o resultado final é que apenas os valores associados à cor "vermelha" sobrevivem à operação:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
e os outros valores são forçados a zero.
Observação: esta fórmula não manipula texto no intervalo de valores. Ver abaixo.
Tratamento de texto em valores
Se você tiver texto em qualquer lugar nos intervalos de valores, a função LARGE lançará um erro #VALUE e interromperá o funcionamento da fórmula.
Para lidar com o texto no intervalo de valores, você pode adicionar a função IFERROR como esta:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Aqui, capturamos erros de GRANDE causados por valores de texto e substituímos por zero. Usar IF dentro de LARGE requer que a fórmula seja inserida com control + shift + enter, então mudamos para SUM em vez de SUMPRODUCT.
Nota: Encontrei esta fórmula postada pelo incrível Barry Houdini no stackoverflow.
