Cuando tenemos que hacer cálculos que suponen condiciones, antes de complicarnos anidando fórmulas una dentro de otra, conviene buscar si existen fórmulas que ya nos provean de las herramientas adecuadas para lograr nuestro objetivo. Afortunadamente, Google Sheets tiene varias de esas fórmulas, que nos permiten simplemente proporcionarle los datos y dejar que la hoja de cálculo trabaje por nosotros. Hoy veremos tres muy útiles: SUMIFS, AVERAGEIFS y COUNTIFS, o en español: SUMAR.SI.CONJUNTO, PROMEDIO.SI.CONJUNTO y CONTAR.SI.CONJUNTO.
Preparando nuestros datos
Lo primero que haremos, como siempre, es preparar algunos datos para poder trabajarlos y explicar las fórmulas que queremos usar. Esta vez tenemos una serie de pedidos con sus costos asociados, y en columnas siguientes la información sobre si están actualmente en ruta, si ya fueron entregados, en qué fecha se deben entregar y finalmente si hubo algún problema en la entrega.
En la parte de arriba de la hoja, tenemos algunos cálculos pendientes por completar, y todos tienen que ver con el concepto de pedido completo. Necesitamos saber el costo total de los pedidos completos, el número de pedidos completos, y el costo promedio de los pedidos completos.
Para efectos de este ejemplo, un pedido completo será aquel que cumpla las siguientes condiciones:
- Está o estuvo en ruta
- Está entregado
- La fecha de entrega ya ha pasado
- No hubo ningún problema con la entrega
Parecen muchas cosas a considerar al mismo tiempo, pero no te preocupes, porque para eso están las fórmulas que aprenderemos hoy.
SUMIFS o SUMAR.SI.CONJUNTO
Intentemos resolver el primer caso: el costo total de los pedidos completos. Para eso, necesitamos conocer una fórmula nueva: SUMIFS o en español SUMAR.SI.CONJUNTO. La sintaxis es:
SUMIFS(rango_suma, rango_condición1, condición1, [rango_condición2, ...], [condición 2, ...])
SUMIFS nos devuelve la suma de un rango en función de varias condiciones o criterios que necesitemos considerar. En este sentido se parece un poco a la fórmula SUMIF o SUMAR.SI, con la diferencia de que SUMIFS (con una S al final) puede contemplar varias condiciones al mismo tiempo en diferentes intervalos separados.
Entonces, para nuestro ejemplo, usemos SUMIFS eligiendo primero el rango del costo como el intervalo a sumar e integremos la primera condición que tenemos para que sume o no: si el rango de “en ruta” es verdadero o no.
=SUMIFS(B6:B15,C6:C15,TRUE)
Aquí es importante apuntar que cada condición se debe escribir como un par de argumentos: cada rango debe llevar su criterio para aplicar y que estos rangos adicionales deben tener el mismo número de filas y columnas entre sí.
Entonces, el siguiente par de argumentos debe contemplar la siguiente condición que tenemos: si los pedidos están marcados como entregados o no. Para eso simplemente agregamos el rango correspondiente a la columna de Entregado y como condición será también TRUE (osea VERDADERO). Fíjate cómo mientras más condiciones aplicamos, más se va especificando la suma:
=SUMIFS(B6:B15,C6:C15,TRUE,D6:D15,TRUE)
En la siguiente condición tenemos queremos contemplar si ya ha pasado la fecha de entrega en relación al día actual, para no tomar en cuenta pedidos que hayan sido entregados antes de tiempo y tampoco pedidos que estén pendientes de entregar en el futuro. Para esto, tomamos usamos el rango de Fecha de entrega y la condición la escribimos utilizando: “<” el signo & para concatenar y la fórmula TODAY(), en español, HOY(). Esto significa que esta fecha debe ser menor que la fecha de hoy, que cumple justo con el requisito que nos propusimos:
=SUMIFS(B6:B15,C6:C15,TRUE,D6:D15,TRUE,E6:E15,"<"&TODAY())
Por último, integremos la condición de que no haya ningún problema en las entregas para poder sumar el costo de un pedido completo o perfecto. Aplicamos el mismo principio, eligiendo el rango correspondiente a Problemas de entrega, y en la condición simplemente escribimos dos comillas dobles para indicar que solo debe aplicar si la celda correspondiente está vacía:
=SUMIFS(B6:B15,C6:C15,TRUE,D6:D15,TRUE,E6:E15,"<"&TODAY(),F6:F15,"")
Todas estas condiciones se aplican al mismo tiempo y se combinan, lo que quiere decir que para que la fórmula sume, deben cumplirse todos los criterios. Esto nos deja con solamente tres de los diez pedidos en un estado ‘perfecto’ o ‘completo’, y es justo la suma que queríamos:
COUNTIFS o CONTAR.SI.CONJUNTO
Bueno, ahora toca el turno de saber el número de pedidos completos. Para eso, usaremos la fórmula COUNTIFS o en español CONTAR.SI.CONJUNTO, que nos permite contar cuántos elementos cumplen con varias condiciones a lo largo de varios rangos al mismo tiempo.
COUNTIFS(rango_condición1, condición1, [rango_condición2, ...], [condición 2, ...])
Esta se comporta igual que SUMIFS, pero ya no necesitamos el rango a sumar, porque solamente contará los elementos que cumplen con las condiciones. Si copiamos las condiciones de la fórmula anterior y las integramos a la fórmula, nos queda así:
=COUNTIFS(C6:C15,TRUE,D6:D15,TRUE,E6:E15,"<"&TODAY(),F6:F15,"")
Y podemos ver que coincide con lo que dijimos hace un momento, solo 3 pedidos cumplen con estas condiciones
AVERAGEIFS o PROMEDIO.SI.CONJUNTO
Por último, necesitamos saber el promedio del costo de estos pedidos completos, y para eso tenemos disponible la fórmula AVERAGEIFS o PROMEDIO.SI.CONJUNTO en español. Esta fórmula se comporta igual que SUMIFS, pero en vez de sumar, calculará el promedio del rango siempre y cuando se cumplan las condiciones estipuladas:
SUMIFS(rango_suma, rango_condición1, condición1, [rango_condición2, ...], [condición 2, ...])
En nuestro caso, podemos de nuevo simplemente copiar las condiciones que ya habíamos construido en SUMIFS y tendremos nuestro resultado:
¡Y listo! Ahora ya sabes cómo usar SUMIFS, COUNTIFS y AVERAGEIFS en tus hojas de cálculo de Google. Estas fórmulas te pueden ahorrar mucho tiempo de cálculos y condiciones anidadas.
Guía en video
Si tienes dudas puedes ver esta misma guía explicada en video:
¡Espero que te haya sido útil esta guía! No olvides seguirnos en YouTube para más tips, trucos y guías de productividad.
Alekz es un apasionado de la tecnología y los videojuegos. Además de ser locutor comercial, disfruta de la expresión artística más sastisfactoria y técnica que existe: programar (y pintar algo de pixel art). Fundó Tesel para ayudar a empoderar a las empresas a desarrollar su máximo potencial mediante la tecnología. Es un absoluto nerd de Star Trek.
Alekz es un apasionado de la tecnología y los videojuegos. Además de ser locutor comercial, disfruta de la expresión artística más sastisfactoria y técnica que existe: programar (y pintar algo de pixel art). Fundó Tesel para ayudar a empoderar a las empresas a desarrollar su máximo potencial mediante la tecnología. Es un absoluto nerd de Star Trek.
