05 Ago Sumar columnas por criterios adyacentes en Excel
Funciones utilizadas: SUMAPRODUCTO
Suponiendo que disponemos del siguiente cuadro con la puntuación por día y actividad para un deportista en el rango A4:I10, donde cada actividad posee a la vez dos valoraciones distintas posibles {A;B;C} y {1;2}:
| Actividad 1 | Actividad 2 | Actividad 3 | Actividad 4 | |||||
| Día 1 | A | 1 | B | 2 | C | 2 | A | 2 |
| Día 2 | B | 1 | A | 2 | C | 2 | B | 1 |
| Día 3 | B | 2 | C | 1 | A | 1 | A | 2 |
| Día 4 | A | 1 | C | 1 | B | 2 | C | 1 |
| Día 5 | C | 2 | A | 1 | A | 1 | C | 2 |
| Día 6 | B | 2 | B | 2 | B | 1 | A | 2 |
Y queremos obtener en el rango K4:N10, la suma de los valores {1;2} para cada valor A, B y C, es decir que tomamos como criterio para sumar las columnas {1;2} a las columnas {A;B;C}, según el siguiente cuadro:
| A | B | C | |
| Día 1 | |||
| Día 2 | |||
| Día 3 | |||
| Día 4 | |||
| Día 5 | |||
| Día 6 |
MÉTODO SUMAPRODUCTO + DOBLE OPERADOR “–“
La sintaxis que debemos aplicar en la celda L5 y que luego debemos arrastrar hacia la derecha y hacia abajo dentro de la matriz es la siguiente:
|
Donde B5:H5 corresponde al rango de fila con todos los valores comenzando desde el primer valor de la puntuación {A;B;C} hasta el último valor que corresponde al mismo tipo de puntuación, L4 corresponde a la celda de referencia para obtener el valor de puntuación {A;B;C} que requerimos para dicha columna, y C5:I5 corresponde al rango de fila con todos los valores comenzando desde el primer valor de la puntuación {1;2} hasta el último valor que corresponde al mismo tipo de puntuación.
Dicha fórmula genera dos matrices de valores que al multiplicarse entre sí permiten obtener el resultado deseado. Por un lado, la primera ecuación funciona como filtro y permite obtener una matriz que identifica con “1” aquellos valores que sí deben ser sumados. La segunda matriz se desplaza una columna hacia la derecha y extrae directamente los valores de la fila. De esta forma al multiplicarse ambas matrices, se simplifican los valores de error (número x texto), y se terminan sumando aquellos valores correctos que fueron multiplicados por 1.
Esta fórmula nos permite calcular de forma rápida y simple, una suma de valores que dependen de una columna adyacente o paralela sin necesidad de realizar cálculos o pasos intermedios.
Te recomiendo ver el siguiente link con más videos de Fórmulas ingeniosas de Excel AQUÍ.
Salvo aclaración, todas las fórmulas y macros de este sitio están configuradas para aplicarse sobre la celda A1. Algunas fórmulas se encuentran encerradas entre llaves {} debido a que son fórmulas matriciales. Estas llaves no deben introducirse tecleándolas, sino que se generan automáticamente al aceptar la fórmula pulsando Control+Shift+Enter al mismo tiempo. Las fórmulas de este sitio son compatibles con versiones de Microsoft Excel® 2010 o superiores.