Suma de total excluyendo celdas vacías.

Tenemos una serie de provincias con sus totales.

Vamos a tener la opción de seleccionar una provincia, o, todas, para ello, en una celda, voy a usar la función APILARV (VSTACK), donde como argumento matriz1, entre comillas dobles, ponemos la palabra todos.

=APILARV("Todos"

Punto y coma, como argumento matriz2, seleccionamos las provincias, decir que las provincias son únicas.

Cerramos paréntesis, y, aceptamos.

=APILARV("Todos";D2:D21)

Tenemos una matriz desbordada, primero con la palabra total, y, después las provincias.

En una celda vamos a crear una validación de datos, para ello, vamos a la pestaña de datos, dentro del grupo herramientas de datos, hacemos clic en validación de datos.

Se abre la ventana de validación de datos, desplegamos permitir, y, seleccionamos lista, en la ventana de origen, seleccionamos las provincias, y, aceptamos.

Selecciono una provincia, y, voy a usar la función SUMAR.SI.CONJUNTO, donde como argumento rango de suma, selecciono los totales.

=SUMAR.SI.CONJUNTO(E2:E21

Punto y coma, como argumento rango de criterios1, seleccionamos la columna de provincias.

=SUMAR.SI.CONJUNTO(E2:E21;D2:D21

Punto y coma, como argumento criterios1, seleccionamos la provincia que hemos elegido.

Cerramos paréntesis y aceptamos.

=SUMAR.SI.CONJUNTO(E2:E21;D2:D21;G2)

Si hemos seleccionado una provincia, y, no Todos, tendremos el total para esa provincia.

Ahora, voy a seleccionar Todos, y, vemos que nos devuelve cero.

Nos devuelve cero, porque la palabra total no se encuentra dentro de las provincias, para solventarlo, después del signo igual, voy a usar el condicional SI, donde como argumento prueba lógica, pregunto si el valor seleccionado es igual a Todos.

=SI(G2="Todos"

Punto y coma, como argumento valor si verdadero, sumamos la columna de total.

=SI(G2="Todos";SUMA(E2:E21)

Punto y coma, como argumento valor si falso, es la función SUMAR.SI,CONJUNTO usada anteriormente.

Cerramos paréntesis y aceptamos.

=SI(G2="Todos";SUMA(E2:E21);SUMAR.SI.CONJUNTO(E2:E21;D2:D21;G2))

Ahora, si tenemos el total.

Vamos a añadir un posible problema, ¿Qué ocurre si borro una provincia?

pues, sigue mostrando el total de todas las provincias, ignorando donde no hay provincia.

Para solventar este problema, lo hare en otra celda.

Usamos la misma función SUMAR.SI.CONJUNTO usada anteriormente.

=SUMAR.SI.CONJUNTO(E2:E21;D2:D21;G2)

En este caso, nos devuelve cero, lo haremos de dos maneras, una mas larga, y, otra mas corta, la primera de ellas va a ser usando los caracteres comodines, sabemos que esta el carácter comodín asterisco (*), que sustituye todos los caracteres, y, el carácter comodín interrogante (?), que solo sustituye un carácter.

Si en el argumento criterios1, pregunto con el condicional SI, si el valor de G2 es igual a Todos.

=SUMAR.SI.CONJUNTO(E2:E21;D2:D21;SI(G2="Todos"

En el argumento valor si verdadero, entre comillas dobles, ponemos el asterisco, quiere decir que, si es igual a Todos, seleccionara todas las celdas de la columna provincia que tenga un carácter o más, por lo que las celdas que estén vacías no las tomara.

=SUMAR.SI.CONJUNTO(E2:E21;D2:D21;SI(G2="Todos";"*"

Punto y coma, en el argumento valor si falso, pues en ese caso, quiere decir que el valor de la celda G2 no es igual a Todos, pues que deje el valor de la celda G2.

Cerramos paréntesis y aceptamos.

=SUMAR.SI.CONJUNTO(E2:E21;D2:D21;SI(G2="Todos";"*";G2))

Tenemos la suma de todas las provincias que tienen un carácter o más.

Si seleccionamos una provincia, pues, tendremos el total de dicha provincia.

Otra forma de hacerlo, algo mas larga, es usar un condicional SI anidado, donde como argumento prueba lógica, pregunto si el valor de la celda G2 es igual a Todos, punto y coma, como argumento valor si verdadero, ponemos otro condicional SI, donde pregunto si la longitud de cada provincia es mayor a cero.

=SI(G2="Todos";SI(LARGO(D2:D21)>0

Punto y coma, como argumento valor si verdadero del segundo SI, que me devuelva la columna de totales.

Cerramos paréntesis del último SI.

=SI(G2="Todos";SI(LARGO(D2:D21)>0;E2:E21)

Punto y coma, como argumento valor si falso del primer SI, ponemos la función SUMAR.SI,CONJUNTO.

Como tengo seleccionado Todos, obtengo una matriz desbordada con el total de cada provincia, y, error donde es blanco.

Como queremos el total, después del signo igual, ponemos la función SUMA.

=SUMA(SI(G2="Todos";SI(LARGO(D2:D21)>0;E2:E21);SUMAR.SI.CONJUNTO(E2:E21;D2:D21;G2)))

Tenemos el mismo resultado que el obtenido anteriormente, si filtramos por una provincia, vemos que tenemos los totales correctos.

Otra forma de hacerlo sería con la función FILTRAR, donde como argumento array, seleccionamos la columna de total.

=FILTRAR(E2:E21

Punto y coma, como argumento Include, seleccionamos la columna de provincias siempre que sea diferente a blanco.

Cerramos paréntesis y aceptamos.

=FILTRAR(E2:E21;D2:D21<>"")

Obtenemos una matriz desbordada con los totales para cada provincia que sea diferente a blanco.

Como queremos el total, después del signo igual, usamos la función SUMA.

=SUMA(FILTRAR(E2:E21;D2:D21<>""))

Ahora, con el condicional SI, pregunto si el valor de la celda G2 es igual a Todos, en ese caso, que ejecute la expresión anterior.

=SI(G2="Todos";SUMA(FILTRAR(E2:E21;D2:D21<>""))

Punto y coma, como argumento valor si falso, pues que ejecute la función SUMAR.SI.CONJUNTO.

Cerramos paréntesis y aceptamos.

=SI(G2="Todos";SUMA(FILTRAR(E2:E21;D2:D21<>""));SUMAR.SI.CONJUNTO(E2:E21;D2:D21;G2))

Vemos que tenemos los mismos resultados.

Tres formas distintas de realizar lo mismo.