Suma por Categoría o Grupo – Excel y Google Sheets

Este tutorial demostrará cómo calcular subtotales por grupo utilizando la función SUMAR.SI.CONJUNTO en Excel y Google Sheets.

 

Tabla de subtotales por categoría o grupo

Este primer ejemplo requiere que usted tenga acceso a la función UNICOS. Esta función está disponible en las nuevas versiones de Excel, Excel 365 o Google Sheets.

Si no tiene acceso a esta función pase a la siguiente sección. Podemos usar la Función UNICOS y la Función SUMAR.SI.CONJUNTO para subtotalizar automáticamente el Número de Productos por Grupo de Productos.

Esta es la Función SUMAR.SI.CONJUNTO final:

=SUMAR.SI.CONJUNTO(C3:C11;B3:B11;E3)

 

Sin embargo, primero añadimos la función UNICOS a la celda E3:

=UNICOS(B3:B11)

 

Cuando se introduce esta fórmula, se crea automáticamente una lista debajo de la celda para mostrar todos los valores únicos encontrados dentro del Grupo de Productos. Se trata de una función de matriz dinámica en la que no es necesario definir el tamaño de la lista de resultados, que se reducirá y aumentará automáticamente a medida que cambien los valores de los datos introducidos.

Ten en cuenta que en Excel 365, la función UNICOS no distingue entre mayúsculas y minúsculas, pero en Google Sheets sí.

Tabla de subtotal por categoría o grupo – Sin la función UNICOS.

Si utilizas una versión de Excel sin la función UNICOS, puedes combinar la función INDICE y la función COINCIDIR con una función CONTAR.SI para crear una fórmula de matriz que produzca una lista de valores únicos de un rango de celdas:

{=INDICE($B$3:$B$11;COINCIDIR(0;CONTAR.SI($E$2:E2;$B$3:$B$11);0))}

 

Para que esta fórmula funcione, las referencias a las celdas fijas deben escribirse con cuidado, con la función CONTAR.SI referenciando el rango $E$2:E2, que es el rango que empieza en E2 hasta la celda que está encima de la celda que contiene la fórmula. La fórmula también necesita ser introducida como una fórmula de matriz presionando CTRL + SHIFT + ENTER después de haberla escrito. Esta fórmula es una fórmula de matriz de 1 celda, que puede copiarse y pegarse en las celdas E4, E5, etc.

No introduzcas esto como una fórmula de matriz para todo el rango E3:E5 en una sola acción. De la misma manera que en el ejemplo anterior, se utiliza una función SUMAR.SI.CONJUNTO para subtotalizar el número de productos por grupo de productos:

=SUMAR.SI.CONJUNTO(C3:C11;B3:B11;E3)

 

Suma por categoría o grupo – Subtotales en tablas de datos

Alternativamente, podemos añadir subtotales directamente en una tabla de datos con las funciones SI y SUMAR.SI.CONJUNTO.

=SI(B3=B2;"";SUMAR.SI.CONJUNTO(C3:C11;B3:B11;B3))

 

Este ejemplo utiliza una función SUMAR.SI.CONJUNTO anidada dentro de una función SI.

Desglosemos el ejemplo en pasos: Comenzamos sumando el Número de Productos que coinciden con el Grupo de Productos correspondiente:

=SUMAR.SI.CONJUNTO(C3:C11;B3:B11;B3)

 

Esta fórmula produce un valor subtotal para cada fila de datos.

Para mostrar los subtotales sólo en la primera fila de datos de cada Grupo de Productos, utilizamos la función SI. Tenga en cuenta que los datos deben estar ya ordenados por Grupo de Productos para que los subtotales se muestren correctamente.

=SI(B3=B2;"";SUMAR.SI.CONJUNTO(C3:C11;B3:B11;B3))

 

La función SI compara el valor del Grupo de Productos de cada fila de datos con la fila de datos que está por encima de ella, y si tienen el mismo valor, muestra una celda en blanco («»).

Si los valores del Grupo de Productos son diferentes, se muestra la suma. De este modo, la suma de cada Grupo de Productos se muestra sólo una vez (en la fila de su primera instancia).

Ordenar los conjuntos de datos por grupo

Si los datos no están ya ordenados, podemos seguir utilizando la misma fórmula para el subtotal.

 

El conjunto de datos anterior no está ordenado por Grupo de Productos, por lo que la columna Subtotal por Grupo muestra cada subtotal más de una vez. Para poner los datos en el formato que queremos, podemos seleccionar la tabla de datos y hacer clic en «Ordenar de la A a la Z».

 

Bloquear las referencias de las celdas

Para que nuestras fórmulas sean más fáciles de leer, hemos mostrado algunas de las fórmulas sin referencias de celdas bloqueadas:

=SI(B3=B2;"";SUMAR.SI.CONJUNTO(C3:C11;B3:B11;B3))

Pero estas fórmulas no funcionarán correctamente cuando se copien y peguen en otro lugar del archivo. En su lugar, debes utilizar referencias de celdas bloqueadas como esta

=SI(B3=B2;"";SUMAR.SI.CONJUNTO($C$3:$C$11;$B$3:$B$11;B3))

Lea nuestro artículo sobre el bloqueo de referencias de celdas para obtener más información.

Uso de tablas dinámicas para mostrar subtotales

Para eliminar el requisito de preordenar los datos por Grupo de Productos, podemos utilizar el poder de las Tablas Dinámicas para resumir los datos en su lugar.

Las Tablas Dinámicas calculan los subtotales automáticamente y muestran los totales y subtotales en varios formatos diferentes.

 

Suma por categoría o grupo en Google Sheets

Estas fórmulas funcionan igual en Google Sheets que en Excel. Sin embargo, la función UNIQUE distingue entre mayúsculas y minúsculas en Google Sheets.