EXCEL: Sumar columnas basadas en criterios adyacentes

Para sumar o subtotalizar columnas basadas en criterios en columnas adyacentes, puede usar una fórmula basada en la función SUMAPRODUCTO.

En el ejemplo que se muestra, la fórmula en L5 es:

=SUMAPRODUCTO(--($B5:$H5=J$4),$C5:$I5)

Cómo funciona esta fórmula

En el núcleo, usa SUMAPRODUCTO para multiplicar y luego sumar productos de dos matrices: matriz1 y matriz2. Array1 está configurado para actuar como un "filtro" para permitir solo valores que cumplan los criterios.

Array1 utiliza un rango que comienza en la primera columna que contiene valores que deben pasar criterios. Estos "valores de criterios" se ubican en una columna a la izquierda e inmediatamente adyacentes a los "valores de datos".

El criterio se aplica como una prueba simple que crea una matriz de valores VERDADERO y FALSO:

--($B5:$H5=J$4)

Este bit de la fórmula "prueba" cada valor en la primera matriz usando los criterios proporcionados, luego usa un doble negativo (-) para forzar los valores VERDADERO y FALSO resultantes a unos y ceros. El resultado se ve así:

{1,0,0,0,1,0,1}

Tenga en cuenta que los 1 corresponden a las columnas 1,5 y 7, que cumplen los criterios de "A".

Para array2 dentro de SUMAPRODUCTO, utilizamos un rango que se "desplaza" una columna a la derecha. Este rango comienza con la primera columna que contiene valores para sumar y termina con la última columna que contiene valores para sumar.

Entonces, en la fórmula de ejemplo en J5, después de que se hayan poblado las matrices, tenemos:

=SUMAPRODUCTO({1,0,0,0,1,0,1},{1,"B",1,"A",1,"A",1})

Como SUMAPRODUCTO está programado específicamente para ignorar los errores que resultan de la multiplicación de valores de texto, la matriz final se ve así:

{1,0,0,0,1,0,1}

Los únicos valores que "sobreviven" a la multiplicación son aquellos que corresponden a 1s dentro de array1. Puede pensar en la lógica en array1 "filtrando" los valores en array2.