Fórmula de Excel: Suma de columnas coincidentes -

Fórmula genérica

=SUMPRODUCT(data*(headers=A1))

Resumen

Para sumar valores en columnas haciendo coincidir los encabezados de columna coincidentes, puede usar una fórmula basada en la función SUMPRODUCTO. En el ejemplo que se muestra, la fórmula en J5 es:

=SUMPRODUCT(data*(LEFT(headers)=J4))

donde "datos" es el rango con nombre B5: G14 y "encabezados" es el rango con nombre B4: G4.

La fórmula suma columnas donde los encabezados comienzan con "a" y devuelven 201.

Explicación

En esencia, esta fórmula se basa en la función SUMPRODUCTO para sumar valores en columnas coincidentes en el rango denominado "datos" C5: G14. Si todos los datos se proporcionaran a SUMPRODUCT en un solo rango, el resultado sería la suma de todos los valores en el rango:

=SUMPRODUCT(data) // all data, returns 387

Para aplicar un filtro haciendo coincidir los encabezados de columna (columnas con encabezados que comienzan con "A"), usamos la función IZQUIERDA de esta manera:

LEFT(headers)=J4) // must begin with "a"

Esta expresión devuelve VERDADERO si el encabezado de una columna comienza con "a" y FALSO si no. El resultado es una matriz:

(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)

Puede ver que los valores 1, 2 y 5 corresponden a columnas que comienzan con "a".

Dentro de SUMPRODUCT, esta matriz se multiplica por "datos". Debido a la transmisión, el resultado es una matriz bidimensional como esta:

(8,10,0,0,7,0;9,10,0,0,10,0;8,6,0,0,6,0;7,6,0,0,6,0;8,6,0,0,6,0;10,11,0,0,7,0;7,8,0,0,8,0;2,3,0,0,3,0;3,4,0,0,4,0;7,7,0,0,4,0)

Si visualizamos esta matriz en una tabla, es fácil ver que solo los valores en las columnas que comienzan con "a" han sobrevivido a la operación, todas las demás columnas son cero. En otras palabras, el filtro mantiene los valores de interés y "cancela" el resto:

A001 A002 B001 B002 A003 B003
8 10 0 0 7 0
9 10 0 0 10 0
8 6 0 0 6 0
7 6 0 0 6 0
8 6 0 0 6 0
10 11 0 0 7 0
7 8 0 0 8 0
2 3 0 0 3 0
3 4 0 0 4 0
7 7 0 0 4 0

Con solo una matriz para procesar, SUMPRODUCT devuelve la suma de todos los valores, 201.

Suma por coincidencia exacta

El ejemplo anterior muestra cómo sumar columnas que comienzan con uno o más caracteres específicos. Para sumar la columna según una coincidencia exacta, puede usar una fórmula más simple como esta:

=SUMPRODUCT(data*(headers=J4))

Articulos interesantes...