Fórmula genérica
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Resumen
Para sumar los n valores principales en un criterio de coincidencia de rango, puede usar una fórmula basada en la función GRANDE, envuelta dentro de la función SUMPRODUCTO. En la forma genérica de la fórmula (arriba), rango representa un rango de celdas que se comparan con criterios , los valores representan valores numéricos de los cuales se recuperan los valores superiores y N representa la idea de valor N-ésimo.
En el ejemplo, la celda activa contiene esta fórmula:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Donde color es el rango con nombre B5: B12 y valor es el rango con nombre C5: C12.
Explicación
En su forma más simple, LARGE devuelve el valor "N-ésimo más grande" en un rango con esta construcción:
=LARGE (range,N)
Así por ejemplo:
=LARGE (C5:C12,2)
devolverá el segundo valor más grande en el rango C5: C12, que es 12 en el ejemplo mostrado.
Sin embargo, si proporciona una "constante de matriz" (por ejemplo, una constante en la forma (1,2,3)) a LARGE como segundo argumento, LARGE devolverá una matriz de resultados en lugar de un solo resultado. Entonces, la fórmula:
=LARGE (C5:C12, (1,2,3))
devolverá el primer, segundo y tercer valor más grande C5: C12 en una matriz como esta: (12,12,10)
Entonces, el truco aquí es filtrar los valores según el color antes de que se ejecute GRANDE. Hacemos esto con la expresión:
(color=E5)
Lo que da como resultado una matriz de valores VERDADERO / FALSO. Durante la operación de multiplicación, estos valores se convierten en unos y ceros:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Entonces, el resultado final es que solo los valores asociados con el color "rojo" sobreviven a la operación:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
y los otros valores se fuerzan a cero.
Nota: esta fórmula no manejará texto en el rango de valores. Vea abajo.
Manejo de texto en valores
Si tiene texto en cualquier lugar de los rangos de valores, la función GRANDE arrojará un error #VALOR y evitará que la fórmula funcione.
Para manejar texto en el rango de valores, puede agregar la función IFERROR como esta:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Aquí, capturamos errores de GRANDE causados por valores de texto y los reemplazamos con cero. Usar IF dentro de LARGE requiere que la fórmula se ingrese con control + shift + enter, por lo que cambiamos a SUM en lugar de SUMPRODUCT.
Nota: Me encontré con esta fórmula publicada por el increíble Barry Houdini en stackoverflow.
