Fórmula genérica
=SUMPRODUCT(data*(range1=criteria1)*(range2=criteria2))
Resumen
Para sumar valores en columnas y filas coincidentes, puede usar la función SUMPRODUCTO. En el ejemplo que se muestra, la fórmula en J6 es:
=SUMPRODUCT(data*(codes=J4)*(days=J5))
donde los datos (C5: G14), los días (B5: B14) y los códigos (C4: G4) se denominan rangos.
Explicación
La función SUMPRODUCT puede manejar matrices de forma nativa, sin requerir la entrada de cambio de control.
En este caso, multiplicamos todos los valores en los datos del rango con nombre por dos expresiones que filtran los valores que no son de interés. La primera expresión aplica un filtro basado en códigos:
(codes=J4)
Dado que J4 contiene "A002", la expresión crea una matriz de valores VERDADERO FALSO como este:
(FALSE,TRUE,FALSE,FALSE,FALSE)
La segunda expresión se filtra por día:
(days=J5)
Dado que J4 contiene "Wed", la expresión crea una matriz de valores VERDADERO FALSO como este:
(FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
En Excel, los valores VERDADERO FALSO se convierten automáticamente en valores 1 y 0 mediante cualquier operación matemática, por lo que la operación de multiplicación convierte las matrices anteriores en unos y ceros, y crea una matriz 2D con las mismas dimensiones que los datos originales. El proceso se puede visualizar como se muestra a continuación:
Finalmente, SUMPRODUCT devuelve la suma de todos los elementos en la matriz final, 9.
Cuenta en lugar de suma
Si desea contar los valores coincidentes en lugar de sumar, puede acortar la fórmula a:
=SUMPRODUCT((codes=J4)*(days=J5)) // count only
Tenga en cuenta que este recuento incluirá celdas vacías.
Notas
- Aunque el ejemplo muestra solo una columna coincidente, esta fórmula sumará correctamente varias columnas coincidentes.
- Si solo necesita hacer coincidir columnas (no filas), puede usar una fórmula como esta.
- Para hacer coincidir solo filas, puede usar la función CONTAR.SI.
