FÓRMULAS DE EXCEL

Sumar columnas por criterios adyacentes en Excel

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 1Actividad 2Actividad 3Actividad 4
Día 1A1B2C2A2
Día 2B1A2C2B1
Día 3B2C1A1A2
Día 4A1C1B2C1
Día 5C2A1A1C2
Día 6B2B2B1A2

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:

 

ABC
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:

=SUMAPRODUCTO(--($B5:$H5=L$4);$C5:$I5)

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.

Gracias a ésta fórmula podemos 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.