La función SUMAR.SI pertenece a la categoría de Matemáticas y trigonométricas.
Esta función suma los valores de un intervalo que cumplan el criterio que especifiquemos.
Sintaxis
SUMAR.SI (rango, criterio, [rango_suma])
Esta función tiene tres argumentos.
Rango. Obligatorio. El rango de celdas que deseamos evaluar. Los valores en blanco y de texto se ignoran. El rango seleccionado puede contener fechas en formato estándar de Excel.
Criterio. Obligatorio. Es el criterio que puede estar en forma de número, expresión, referencia de celda, texto o función que determina las celdas que se van a sumar. Se pueden incluir caracteres comodines: un signo de interrogación (?) para que coincida con cualquier carácter único, o un asterisco (*) para que coincida con cualquier secuencia de caracteres. Si deseamos buscar un signo de interrogación o un asterisco real, debemos de escribir una tilde (~) antes del carácter.
Por ejemplo, "*~?".
Rango_suma. Opcional. Son las celdas que se sumarán, si es que desea sumar celdas a las ya especificadas en el argumento rango. Si omite el argumento rango_suma, Excel suma las celdas especificadas en el argumento rango (las mismas celdas a las que se aplica el criterio).
Rango_suma debe tener el mismo tamaño y forma que rango. Si no es así, el rendimiento puede no ser correcto, es decir, que los valores que nos devuelvan no serán los esperados.
Veamos un ejemplo, tenemos el siguiente modelo.
En la parte inferior, vemos un modelo con una serie de porcentajes, que corresponde a cada vendedor.
También, vemos que la columna de comisiones no este rellena, pues, lo primero que vamos a calcular la comisión de cada vendedor respecto al porcentaje que le corresponda.
Debemos de preguntar el vendedor de la celda B2, en este caso, después vamos a ir a buscar el porcentaje en el cuadro inferior.
Para ello, vamos a usar el condicional SI.
Como argumento prueba lógica del condicional SI, pues, preguntamos si el valor de la celda B2 es igual al valor de G18, que contiene el valor de Jaime, la cual debemos de convertir en referencia absoluta, para que cuando arrastremos hacia abajo, no se actualice., también debemos de convertir en referencia absoluta, aquellas celdas que contienen los porcentajes. en caso, que el valor sea Jaime, multiplicamos le valor de la celda D2, lo multiplicamos por el porcentaje que le corresponda.
=SI(B2=$G$18;D2*H16
Escribimos punto y como, para pasar al argumento valor si falso, pues si no es Jaime, vamos a preguntar si el valor de dicha celda es igual a Claudia, si lo es, multiplicamos el valor de la celda D2 por el porcentaje que le corresponda, quedando de la siguiente manera:
=SI(B2=$G$18;D2*$H$16;SI(B2=$G$16;D2*$H$16
Si tampoco lo es, vamos a preguntar si el valor de la celda B2 es igual a Dolores.
=SI(B2=$G$18;D2*$H$16;SI(B2=$G$16;D2*$H$16;SI(B2=$G$17;D2*$H$17)))
Si tampoco lo es, vamos a preguntar si es Luis.
=SI(B2=$G$18;D2*$H$16;SI(B2=$G$16;D2*$H$16;SI(B2=$G$17;D2*$H$17;SI(B2=$G$19;D2*$H$19
Y, si, por último, tampoco lo es, preguntamos si es Miguel.
=SI(B2=$G$18;D2*$H$16;SI(B2=$G$16;D2*$H$16;SI(B2=$G$17;D2*$H$17;SI(B2=$G$19;D2*$H$19;SI(B2=$G$20;D2*$H$20)))))
Aceptamos, y, arrastramos hacia abajo, y, ya tenemos los porcentajes correspondientes a cada vendedor.
Lo siguiente que quiero es:
El primer argumento de la función SUMAR.SI, es rango de criterios, que este caso es la columna de cantidades.
=SUMAR.SI(D2:D20
El siguiente argumento es criterio, pues es que dichas ventas sean mayores a 5000, recordar, que debe de ir entre comillas dobles.
=SUMAR.SI(D2:D20;">5000"
El último argumento, que es opcional, es rango de suma, si lo omitimos, va a sumar la misma columna que hemos especificado en el argumento rango de criterios, pero en este caso, si debe de sumar la misma columna, por lo que la omitimos.
=SUMAR.SI(D2:D20;">5000")
Aceptamos, y, ya vemos el total de cantidades superiores a 5000.
Y, si en vez de ver el total, quiero que queden señaladas dentro de la columna de cantidades, queden resaltadas de otro color, aquellas celdas que sean superiores a 5000.
Pues, para ello, vamos a hacer uso del formato condicional.
Desde la pestaña de inicio, desplegamos formato condicional, y, seleccionamos nueva regla.
En la ventana que se abre, seleccionaos la última opción, que es introducir una formula.
En este caso, voy a hacer uso del condicional SI, donde voy a preguntar que si el valor de la celda D2 es mayor de 5000, pues que resalte dicha celda, vemos que la referencia esta como relativa, de esa manera, al aceptar, se comprobara cada celda del rango seleccionado empezando desde la celda D2.
Aceptamos, y, vemos como han quedado resaltadas aquellas celdas dentro del rango seleccionado, que son superiores a 5000.
Pero, si en vez de saber el total de cantidades, quiero saber el total de comisiones cuyas cantidades han sido superior a 5000, pues, debo de añadir el argumento rango de suma, y, seleccionar la columna de porcentajes.
Igual, que hemos hecho anteriormente, en vez de seleccionar los rangos, podemos crear nombres de rangos.
Ahora, quiero saber las comisiones de ventas mayores a 100, pero del comercial Jaime.
¿Cómo lo hacemos si la función SUMAR.SI solo acepta un criterio?
Pero si conocemos el condicional SI, podemos usarlo junto con la función SUMA que sería lo mismo, pues la función SUMAR.SI, es la unión de ambas funciones.
Bien, debo de hacer dos preguntas, una que las comisiones sean superiores a 100, y, otra que el comercial sea Jaime, cosa que puedo hacer con un SI, usando el método visto anteriormente, para especificar más de un criterio, haciendo uso de los paréntesis, y, del signo de asterisco, quedando de la siguiente manera:
=SI((E2:E20>100) *(B2:B20="Jaime"); E2:E20)
O sea que si en la matriz E2:E20 es superior a 100, y, en la matriz B2:B20 es igual a Jaime, debe de poner el rango E2:E20, esto nos va a devolver más de un valor, es decir, todas las coincidencias, pero como lo que queremos es un total, delante del SI ponemos la función SUMA de la siguiente manera:
=SUMA (SI ((E2:E20>100) *(B2:B20="Jaime"); E2:E20))
Ya tenemos el resultado de las comisiones superiores a 100 del comercial Jaime, pero podíamos decir de forma manual.
Ahora, quiero ver las ventas de aquellos comerciales cuya letra final del nombre sea la “s”, para ello, he de usar el carácter comodín asterisco primero, y, después poner la letra S.
=SUMAR.SI (B2:B20;"*s"; D2:D20)
Vemos como el criterio es “*s”.
El asterisco sustituirá todas las letras que haya antes de la “s”.
Lo siguiente que voy a hacer es crear un desplegable, para seleccionar un trimestre, y, quiero calcular la diferencia porcentual del trimestre seleccionado respecto a todos los trimestres.
Para ello, vamos a crear una lista dentro de validación de datos, desplegamos permitir, y, elegimos lista, en este caso, en la ventana origen, vamos a escribir los valores, dichos valores deben de ir separados por punto y coma.
Aceptamos.
Seleccionamos un trimestre.
Primero, vamos a hacer uso de la función SUMAR.SI, para calcular las cantidades para el trimestre seleccionado.
=SUMAR.SI(C2:C20;H22;D2:D20)
Ahora, debemos de dividir este resultado entre la suma total, para ello, hacemos uso de la función SUMA.
=SUMAR.SI(C2:C20;H22;D2:D20)/SUMA(D2:D20)
Lo ponemos en formato de porcentaje, y, damos dos decimales, y, ya tenemos el porcentaje del trimestre que seleccionemos respecto al total de cantidades.
Lo siguiente que quiero saber las cantidades vendidas del comercial Jaime, y, Claudia.
En este caso, igual que antes, lo vamos a ser por separado, es decir, vamos a usar el condicional SI, donde como prueba lógica, usamos el operador lógico O, que ya sabemos que devuelve verdadero, si una de las condiciones se cumple, lo vamos a usar para preguntar por el nombre de Jaime, o, Claudia, como argumento valor si verdadero, haremos que nos devuelva la columna de cantidades, pero esto nos va a devolver una matriz desbordada, que ya sabemos, que es una fórmula que devuelve más de un valor, pero como queremos el total, pues, después del signo igual hacemos uso de la función SUMA.
=SUMA(SI(O(B2:B20="Jaime";B2:B20="Claudia");D2:D20))
SUMAR.SI.CONJUNTO
Seguimos trabajando con el modelo anterior, pero sin la columna de comisiones.
La función SUMAR.SI.CONJUNTO, va a sumar aquellas celdas que cumplan con un criterio, o, más, aunque si solo tenemos un criterio, podeos usar la función SUMAR.SI.
Sintaxis
SUMAR.SI.CONJUNTO(rango_suma; rango_criterios1; criterios1; [rango_criterios2; criterios2];...)
En la sintaxis podemos observar a diferencia de la función SUMAR.SI, que el argumento rango_suma es el primero, mientras que en la función SUMAR.SI es el último argumento.
· Rango_suma. Obligatorio. Es el rango por el que se van a sumar las coincidencias.
· Rango_criterios1. Obligatorio. es el rango por donde se va a buscar el primer criterio.
Desde rango_criterios2 en adelante son opcionales, podemos poner hasta 127 condiciones.
Veamos un ejemplo, quiero saber lo que ha ganado el comercial Jaime durante el trimestre1, es decir, desde el día 01/01/2020 hasta el 31/03/2020.
Tenemos dos condiciones, por lo que SUMAR.SI no nos valdría, vamos a usar SUMAR.SI.CONJUNTO.
Como rango de suma va a ser las ventas, como rango_criterios1, va a ser el rango de comercial, el criterio va a ser Jaime, como rango_criterios2, va a ser las fechas, como criterio2 va a ser “>=01/01/2020”, como rango_criterios3, vuelve a ser la fecha, y, como criterio3 va a ser “<=31/03/2020”.
Quedaría de la siguiente manera:
=SUMAR.SI.CONJUNTO(D2:D20; B2:B20; B2; A2:A20;">=01/01/2020"; A2:A20;"<=31/03/2020")
Pero, que ocurre, si quiero totalizar por el comercial Jaime, pero para el trimestre 1, y, trimestre 2.
Bien, si uso la siguiente expresión:
=SUMAR.SI.CONJUNTO(D2:D20;C2:C20;"T1";C2:C20;"T2"
Nunca obtendré un resultado, ¿Por qué?, pues porque va a buscar en la columna de trimestres un valor que sea igual a T1, y, a T2, porque todas las condiciones que pongamos dentro de la función SUMAR.SI.CONJUNTO, se deben de cumplir.
Para solventarlo, puedo usar la expresión anterior para el cálculo del trimestre 1 para el comercial Jaime.
=SUMAR.SI.CONJUNTO(D2:D20;C2:C20;"T1";B2:B20;"Jaime")
Obtenemos el mismo resultado que la expresión anterior, porque es la misma.
Lo siguiente que voy a hacer es poner el símbolo “+” después de esta función, y, coloco la misma expresión que la anterior, pero cambio el criterio T1 a T2.
=SUMAR.SI.CONJUNTO(D2:D20;C2:C20;"T1";B2:B20;"Jaime")+SUMAR.SI.CONJUNTO(D2:D20;C2:C20;"T2";B2:B20;"Jaime")
Ahora, si tengo las ventas para el comercial Jaime de los trimestres 1, y, 2.
En el siguiente ejemplo, queremos ver las ventas de aquellos comerciales cuyo nombre empiece por la letra C.
Aquí deberemos de hacer uso de los caracteres comodines.
Como solo tenemos un criterio, también, podía haber hecho uso de la función SUMAR.SI, debemos de recordar, que para la función SUMAR.SI, el argumento rango de suma, es el último argumento, a diferencia de la función SUMAR.SI.CONJUNTO, que es el primer argumento.
=SUMAR.SI.CONJUNTO(D2:D20;B2:B20;"C*")
Aceptamos, y, tenemos las ventas de los comerciales que comienzan por la letra C.
Lo siguiente que quiero saber, es el total de ventas para aquellos comerciales que no se llaman Claudia, para el trimestre 4.
Para ello, debemos de hacer uso del operador de comparación indistinto “<>”.
Uso la siguiente expresión, donde podemos ver como en criterio 1, hemos usado el operador de comparación indistinto, es decir, que nos va a dar los comerciales que no se llaman Claudia.
=SUMAR.SI.CONJUNTO(D2:D20;B2:B20;"<>Claudia";C2:C20;"T4")
Debemos de decir, que cuando en el argumento rango de suma, usamos los valores VERDADERO, o, FALSO, el resultado no será el esperado, porque cuando usamos el valor de VERDADERO (TRUE), se evalúa como uno, y, cuando usamos FALSO (FALSE), se evalúa como cero, por lo que el resultado puede ser el no esperado.
Comentarios