Descarga el libro de trabajo de ejemplo
Este tutorial demostrará cómo usar la función SUMIFS para sumar datos relacionados con celdas que no están en blanco o no están vacías en Excel y Google Sheets.
Suma si no está en blanco
Primero, demostraremos cómo sumar datos relacionados con celdas que no están en blanco.
Podemos usar la función SUMIFS para sumar todos Puntuaciones por Jugadores con nombres que no estén en blanco.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Para sumar filas con celdas que no están en blanco, excluimos Puntuaciones con falta Jugador nombres. Usamos los criterios "no es igual a espacio en blanco" ("") dentro de la función SUMIFS.
Tratar espacios como celdas en blanco: con columna auxiliar
Debe tener cuidado al interactuar con celdas en blanco en Excel. Las celdas pueden aparecer en blanco para usted, pero Excel no las tratará como en blanco. Esto puede ocurrir si la celda contiene espacios, saltos de línea u otros caracteres invisibles. Este es un problema común al importar datos a Excel desde otras fuentes.
Si necesitamos tratar las celdas que solo contienen espacios de la misma manera que si estuvieran en blanco, la fórmula del ejemplo anterior no funcionará. Observe cómo la fórmula SUMIFS no considera que la celda B9 a continuación ("") esté en blanco:
| 1 | = SUMIFS (D3: D9, B3: B9, "") |
Para tratar una celda que contiene solo espacios como si fuera una celda en blanco, podemos agregar una columna auxiliar usando las funciones LEN y TRIM para identificar Jugadores con nombres.
La función TRIM elimina los espacios adicionales del inicio y el final del valor de cada celda y la función LEN luego cuenta el número de caracteres restantes. Si el resultado de la función LEN es 0, entonces el Jugador El nombre debe haber estado en blanco o solo tener espacios:
| 1 | = LARGO (RECORTE (B3)) |
Aplicamos la función SUMIFS a la columna auxiliar (sumando si es mayor que 0), y ahora calcula la suma con precisión.
| 1 | = SUMIFS (E3: E9, D3: D9, "> 0") |
La columna de ayuda es fácil de crear y de leer, pero es posible que desee tener una fórmula única para realizar la tarea. Esto se trata en la siguiente sección.
Tratar espacios como celdas en blanco, sin columna auxiliar
Si es necesario tratar las celdas que contienen solo espacios de la misma manera que si estuvieran en blanco, pero el uso de una columna auxiliar no es apropiado, entonces podemos utilizar la función SUMPRODUCT en combinación con las funciones LEN y TRIM para sumar datos relacionados con las celdas. que contiene no en blanco Jugador nombres:
| 1 | = SUMPRODUCTO (- (LEN (TRIM (B3: B9))> 0), D3: D9) |
En este ejemplo, usamos la función SUMPRODUCT para realizar cálculos complicados de "suma si". Repasemos la fórmula.
Esta es nuestra fórmula final:
| 1 | = SUMPRODUCTO (- (LEN (TRIM (B3: B9))> 0), D3: D9) |
Primero, la función SUMPRODUCT enumera la matriz de valores de los dos rangos de celdas:
| 1 | = SUMPRODUCTO (- (LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""}))> 0), {25; 10; 15; 5 ; 8; 17; 50) |
Luego, la función TRIM elimina los espacios iniciales y finales de Jugador nombres:
| 1 | = SUMPRODUCTO (- (LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""})> 0), {25; 10; 15; 5; 8; 17; 50) |
La función LEN calcula las longitudes del recortado Jugador nombres:
| 1 | = SUMPRODUCTO (- ({1; 1; 0; 1; 0; 2; 0}> 0), {25; 10; 15; 5; 8; 17; 50) |
Con la prueba lógica (> 0), cualquier recorte Jugador los nombres con más de 0 caracteres se cambian a VERDADERO:
| 1 | = SUMPRODUCTO (- ({VERDADERO; VERDADERO; FALSO; VERDADERO; FALSO; VERDADERO; FALSO}), {25; 10; 15; 5; 8; 17; 50) |
A continuación, los guiones dobles (-) convierten los valores VERDADERO y FALSO en 1 y 0:
| 1 | = SUMPRODUCTO ({1; 1; 0; 1; 0; 1; 0}, {25; 10; 15; 5; 8; 17; 50) |
La función SUMPRODUCT luego multiplica cada par de entradas en las matrices para producir una matriz de Puntuaciones solo para Jugador nombres que no están en blanco o que no están hechos solo de espacios:
| 1 | = SUMPRODUCTO ({25; 10; 0; 5; 0; 17; 0) |
Finalmente, los números de la matriz se suman
| 1 | =57 |
Puede encontrar más detalles sobre el uso de declaraciones booleanas y el comando "-" en una función SUMPRODUCT aquí
Suma si no está en blanco en Hojas de cálculo de Google
Estas fórmulas funcionan exactamente igual en Google Sheets que en Excel.
