Fórmula genérica
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Resumo
Para somar os valores n inferiores em um critério de correspondência de intervalo, você pode usar uma fórmula de matriz baseada na função SMALL, incluída na função SUM. Na forma genérica da fórmula (acima), intervalo1 representa o intervalo de células em comparação com os critérios , intervalo2 contém valores numéricos a partir da qual os valores de fundo são recuperados, e N representa "enésimo".
No exemplo, a célula ativa contém esta fórmula:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Onde cor é o intervalo nomeado B5: B12 e valor é o intervalo nomeado C5: C12.
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Explicação
Em sua forma mais simples, SMALL retorna o valor "enésimo menor" em um intervalo com esta construção:
=SMALL (range,N)
Então, por exemplo:
=SMALL (C5:C12,2)
retornará o segundo menor valor no intervalo C5: C12, que é 5 no exemplo mostrado.
No entanto, se você fornecer uma "constante de matriz" (por exemplo, uma constante na forma (1,2,3)) para SMALL como o segundo argumento, SMALL retornará uma matriz de resultados em vez de um único resultado. Então, a fórmula:
=SMALL (C5:C12, (1,2,3))
retornará o 1º, 2º e 3º menor valor C5: C12 em uma matriz como esta: (4,5,7).
Portanto, o truque aqui é filtrar os valores com base na cor antes que o PEQUENO seja executado. Fazemos isso com uma expressão baseada na função IF:
IF(color=E5,value)
Isso constrói a matriz de valores alimentados em SMALL. Essencialmente, apenas os valores associados à cor vermelha entram no array. Onde a cor é igual a "vermelho", a matriz contém um número, e onde a cor não é vermelha, a matriz contém FALSO:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
A função SMALL ignora os valores FALSE e retorna os 3 menores valores na matriz: (4,8,10). A função SUM retorna o resultado final, 22.
