Descarga el libro de trabajo de ejemplo
Este tutorial demostrará cómo usar las funciones SUMPRODUCT y SUMIFS para sumar datos que cumplen con ciertos criterios en varias hojas en Excel y Google Sheets.
Suma regular en varias hojas
A veces, sus datos pueden abarcar varias hojas de trabajo en un archivo de Excel. Esto es común para los datos que se recopilan periódicamente. Cada hoja de un libro de trabajo puede contener datos para un período de tiempo establecido. Queremos una fórmula que sume los datos contenidos en dos o más hojas.
La función SUMA le permite sumar fácilmente datos en varias hojas utilizando un Referencia 3D:
| 1 | = SUMA (Hoja1: Hoja2! A1) |
Sin embargo, esto no es posible con la función SUMIFS. En su lugar, debemos utilizar una fórmula más complicada.
Suma si en varias hojas
Este ejemplo sumará el Número de entregas planificadas para cada Cliente en varias hojas de trabajo, cada una con datos relacionados con un mes diferente, mediante el uso de las funciones SUMIFS, SUMPRODUCT e INDIRECT:
| 1 | = SUMPRODUCTO (SUMIFS (INDIRECTO ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECTO ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Repasemos esta fórmula.
Paso 1: Cree una fórmula SUMIFS solo para 1 hoja de entrada:
Usamos la función SUMIFS para sumar el Número de entregas planificadas por Cliente para una sola hoja de datos de entrada:
| 1 | = SUMIFS (D3: D7, C3: C7, H3) |
Paso 2: agregar una referencia de hoja a la fórmula
Mantenemos el resultado de la fórmula igual, pero especificamos que los datos de entrada están en la hoja llamada 'Paso 2'
| 1 | = SUMIFS ('Paso 2'! D3: D7, 'Paso 2'! C3: C7, H3) |
Paso 3: anidar dentro de una función SUMPRODUCT
Para preparar la fórmula para realizar cálculos SUMIFS en varias hojas y luego sumar los resultados, agregamos una función SUMPRODUCT alrededor de la fórmula
| 1 | = SUMPRODUCTO (SUMIFS ('Paso 3'! D3: D7, 'Paso 3'! C3: C7, H3)) |
El uso de la función SUMIFS en una hoja produce un solo valor. En varias hojas, la función SUMIFS genera una matriz de valores (uno para cada hoja de trabajo). Usamos la función SUMPRODUCT para sumar los valores en esta matriz.
Paso 4: Reemplace la referencia de hoja con una lista de nombres de hoja
Deseamos reemplazar el Nombre de la hoja parte de la fórmula con una lista de datos que contiene los valores: ene, feb, mar, y abr. Esta lista se almacena en las celdas F3: F6.
La función INDIRECTA asegura que la lista de texto que muestra Nombres de hojas se trata como parte de una referencia de celda válida en la función SUMIFS.
| 1 | = SUMPRODUCTO (SUMIFS (INDIRECTO ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECTO ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
En esta fórmula, la referencia de rango escrita anteriormente:
| 1 | 'Paso 3'! D3: D7 |
Es reemplazado por:
| 1 | INDIRECTO ("'" & F3: F6 & "'!" & "D3: D7") |
Las comillas dificultan la lectura de la fórmula, por lo que aquí se muestra con espacios agregados:
| 1 | INDIRECTO ("'" & F3: F6 & "'!" & "D3: D7") |
El uso de esta forma de hacer referencia a una lista de celdas también nos permite resumir datos de varias hojas que no siguen un estilo de lista numérica. Una referencia 3D estándar requeriría que los nombres de las hojas estén en el estilo: Entrada1, Entrada2, Entrada3, etc., pero el ejemplo anterior le permite usar una lista de cualquier Nombres de hojas y hacer referencia a ellos en una celda separada.
Referencias de celda de bloqueo
Para que nuestras fórmulas sean más fáciles de leer, mostramos las fórmulas sin referencias de celda bloqueadas:
| 1 | = SUMPRODUCTO (SUMIFS (INDIRECTO ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECTO ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Pero estas fórmulas no funcionarán correctamente cuando se copien y peguen en otro lugar de su archivo. En su lugar, debe usar referencias de celda bloqueadas como esta:
| 1 | = SUMPRODUCTO (SUMIFS (INDIRECTO ("'" & $ F $ 3: $ F $ 6 & "'!" & "D3: D7"), INDIRECTO ("'" & $ F $ 3: $ F $ 6 & "'!" & "C3: C7"), H3)) |
Lea nuestro artículo sobre Referencias de celdas bloqueadas para obtener más información.
Suma si en varias hojas de Google Sheets
El uso de la función INDIRECTO para hacer referencia a una lista de hojas en una función SUMPRODUCT y SUMIFS no es posible actualmente en Google Sheets.
En su lugar, se pueden hacer cálculos SUMIFS separados para cada hoja de entrada y los resultados se suman:
| 1234 | = SUMIFS (¡Ene! D3: D7, Ene! C3: C7, H3)+ SUMIFS (¡Feb! D3: D7, Feb! C3: C7, H3)+ SUMIFS (Mar! D3: D7, Mar! C3: C7, H3)+ SUMIFS (¡Abr! D3: D7, Abr! C3: C7, H3) |
