Este tutorial explica la diferencia entre las funciones SUMIF y SUMIFS en términos de su sintaxis y y proporciona una serie de ejemplos de fórmulas para sumar valores con varios criterios Y / O en Excel 2019, 2016, 2013, 2010, 2007, 2003 y versiones anteriores.
Como todos saben, Microsoft Excel proporciona una matriz de funciones para realizar varios cálculos con datos. Hace algunos artículos, exploramos COUNTIF y COUNTIFS, que están diseñados para contar células en función de una sola condición y varias condiciones, respectivamente. La semana pasada cubrimos Excel SUMIF que agrega valores que cumplen con los criterios especificados. Ahora es el momento de repasar la versión plural de SUMIF – Excel SUMIFS que permite sumar valores por múltiples criterios.
Aquellos que estén familiarizados con la función SUMIF podrían pensar que convertirla a SUMIFS solo requiere un extra «S» y algunos criterios adicionales. Esto parecería bastante lógico … pero «lógico» no siempre es así cuando se trata de Microsoft 🙂
- Función SUMIF – sintaxis & uso
- Función SUMIFS – sintaxis & uso
- Cómo usar SUMIFS en Excel (Y lógica)
- Excel SUMIF con varios criterios (lógica OR)
- Excel SUMIFS con varias condiciones OR
- Uso de Excel SUM en fórmulas de matriz
Función SUMIF de Excel – sintaxis & uso
La función SUMIF se usa para sumar valores condicionalmente según un criterio único. Discutimos la sintaxis SUMIF en detalle en el artículo anterior, así que permítame darle un breve resumen ahora.
-
range: el rango de celdas que se evaluarán según sus criterios, obligatorio. -
criteria: la condición que debe cumplirse, requerido. -
sum_range– las celdas a sumar si se cumple la condición, opcional.
Como puede ver, el La sintaxis de la función SUMIF de Excel solo permite una condición. Y aún así, decimos que Excel SUMIF se puede usar para sumar valores con múltiples criterios. ¿Como puede ser? Añadiendo los resultados de varias funciones SUMIF y utilizando fórmulas SUMIF con criterios de matriz, como se demuestra en los ejemplos que siguen.
Función SUMIFS de Excel – sintaxis & uso
Utiliza SUMIFS en Excel para encontrar una suma condicional de valores basada en varios criterios. La función SUMIFS se introdujo en Excel 2007, por lo que puede usarla en todas las versiones modernas de Excel 2019, 2016, 2013, 2010 y 2007.
En comparación con SUMIF, la sintaxis SUMIFS es un poco más compleja :
Los primeros 3 argumentos son obligatorios, los rangos adicionales y sus criterios asociados son opcionales.
-
sum_range: se requiere una o más celdas para sumar. Puede ser una sola celda, un rango de celdas o un rango con nombre. Solo se suman las celdas con números; los valores en blanco y de texto se ignoran. -
criteria_range1: el primer rango que se evaluará según los criterios asociados, obligatorio. -
criteria1: la primera condición que se debe cumplir, obligatoria. Puede proporcionar los criterios en forma de número, expresión lógica, referencia de celda, texto u otra función de Excel. Por ejemplo, puede utilizar criterios como 10, «> = 10», A1, «cerezas» o TODAY (). -
criteria_range2, criteria2, …: estos son rangos y criterios adicionales asociados con ellos, opcional. Puede usar hasta 127 pares de rango / criterio en fórmulas SUMIFS.
Y ahora, vamos a » s eche un vistazo a la fórmula de Excel SUMIFS con dos condiciones. Suponga que tiene una tabla que enumera los envíos de frutas de diferentes proveedores. Tiene los nombres de las frutas en la columna A, los nombres de los proveedores en la columna B y la cantidad en la columna C. Lo que desea es encontrar una suma de cantidades relacionadas con una fruta y un proveedor determinados, p. Ej. todas las manzanas proporcionadas por Pete.
Cuando estás aprendiendo algo nuevo, siempre es una buena idea comenzar con cosas simples. Entonces, para empezar, definamos todos los argumentos para nuestra fórmula SUMIFS:
- rango_suma – C2: C9
- rango_criterios1 – A2: A9
- criterio1 – «manzanas»
- rango_criterio2 – B2: B9
- criterio2 – «Pete»
Ahora reúna los parámetros anteriores y obtendrá la siguiente fórmula SUMIFS:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Para refinar Además de la fórmula, puede reemplazar los criterios de texto «manzanas» y «Pete» con referencias de celda.En este caso, no tendrá que cambiar la fórmula para calcular la cantidad de otras frutas de un proveedor diferente:
=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)
Uso de SUMIFS y SUMIF en Excel: cosas para recordar
Dado que el objetivo de este tutorial es cubrir todas las formas posibles de sumar valores por varias condiciones, discutiremos ejemplos de fórmulas con ambas funciones: Excel SUMIFS y SUMIF con varios criterios. Para usarlos correctamente, debe comprender claramente qué tienen estas dos funciones en común y en qué se diferencian.
Si bien la parte común es clara (destino y parámetros similares), las diferencias no son tan obvios, aunque muy esenciales.
1. El orden de los argumentos
En las funciones SUMIF y SUMIFS de Excel, el orden de los argumentos es diferente. En particular, sum_range es el primer en SUMIFS, pero es el tercero en SUMIF.
A primera vista, puede parecer que Microsoft complica deliberadamente la curva de aprendizaje de sus usuarios. Howev er, al mirar más de cerca, verá el razonamiento detrás de esto. El punto es que sum_range es opcional en SUMIF. Si lo omite, no hay problema, su fórmula SUMIF sumará valores en el rango (primer parámetro).
En SUMIFS, el rango suma es muy importante y obligatorio, y por eso es lo primero. Probablemente los chicos de Microsoft pensaron que después de agregar el par de rango / criterio 10 o 100, alguien podría olvidar especificar el rango para sumar:)
2. El rango de suma y el rango de criterios deben tener el mismo tamaño
En la función SUMIF, el argumento rango_suma no tiene que ser necesariamente del mismo tamaño que el argumento de rango, siempre que tenga la celda superior izquierda a la derecha. En Excel SUMIFS, cada rango_criterios debe contener el mismo número de filas y columnas que el parámetro rango_suma.
Por ejemplo, =SUMIF(A2:A9,F1,C2:C18) devolverá el resultado correcto porque Excel considera solo la celda superior a la izquierda en el argumento rango_sum (C2 en este ejemplo, que es correcto), y luego incluye tantas columnas y filas como contenga el argumento rango.
Muy bien, suficiente estrategia (es decir, teoría) , entremos en las tácticas (es decir, ejemplos de fórmulas:)
Cómo usar SUMIFS en Excel – ejemplos de fórmulas
Hace un momento, discutimos una fórmula simple de SUMIFS con dos textos criterios. De la misma manera, puede utilizar Excel SUMIFS con múltiples criterios expresados por números, fechas, expresiones lógicas y otras funciones de Excel.
Ejemplo 1. Excel SUMIFS con operadores de comparación
En nuestra tabla de proveedores de frutas, suponga que desea sumar todas las entregas de Mike con Cant. 200 o más. Para hacer esto, use el operador de comparación «mayor que o r es igual a «(> =) en el criterio2 y obtiene la siguiente fórmula SUMIFS:
=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")
Cubrimos todos los operadores de comparación posibles en detalle cuando hablamos de la función SUMIF de Excel, los mismos operadores se puede utilizar en los criterios SUMIFS. Por ejemplo, la siguiente fórmula devuelve la suma de todos los valores en las celdas C2: C9 que son mayores o iguales que 200 y menores o iguales que 300.
=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")
Ejemplo 2. Usar Excel SUMIFS con fechas
En caso de que desee sumar valores con varios criterios basados en la fecha actual, use la función TODAY () en su SUMIFS criterios, como se demuestra a continuación. La siguiente fórmula suma valores en la columna D si una fecha correspondiente en la columna C se encuentra dentro de los últimos 7 días, incluido el día de hoy:
=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())
De manera similar, puede usar la función SUMIF de Excel para sumar valores en un rango de fechas determinado. Por ejemplo, la siguiente fórmula SUMIFS agrega los valores en las celdas C2: C9 si una fecha en la columna B cae entre el 1 de octubre de 2014 y el 31 de octubre de 2014, ambos inclusive.
=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")
Se puede lograr el mismo resultado calculando la diferencia de dos funciones SUMIF, como se demuestra en este ejemplo: Cómo usar SUMIF para sumar valores en un rango de fechas dado. Sin embargo, Excel SUMIFS es mucho más fácil y comprensible, ¿no es así?
Ejemplo 3. Excel SUMIFS con celdas en blanco y no en blanco
Al analizar informes y otros datos, a menudo es posible que necesite sumar valores correspondientes a celdas vacías o no vacías.
Y ahora, veamos cómo puede usar una fórmula SUMIFS con criterios «en blanco» y «no en blanco» en datos.
Suponga que tiene una fecha de pedido en la columna B, la fecha de entrega en la columna C y Cant. en la columna D. ¿Cómo encuentra el total de productos que aún no se han entregado? Es decir, desea conocer la suma de los valores correspondientes a las celdas no vacías en la columna B y las celdas vacías en la columna C.
La solución es utilizar la fórmula SUMIFS con 2 criterios:
=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")
Usando Excel SUMIF con múltiples criterios OR
Como se señaló al principio de este tutorial, la función SUMIFS está diseñada con lógica AND. Pero, ¿qué sucede si necesita sumar valores con varios criterios OR, es decir, cuando se cumple al menos una de las condiciones?
Ejemplo 1. SUMIF + SUMIF
La solución más simple es sumar los resultados devueltos por varias funciones SUMIF. Por ejemplo, la siguiente fórmula demuestra cómo encontrar el total de productos entregados por Mike y John:
=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)
Como puede ver, la primera función SUMIF agrega las cantidades correspondientes a «Mike», la otra función SUMIF devuelve las cantidades relacionadas con «John» y luego agrega estos 2 números.
Ejemplo 2. SUM & SUMIF con un argumento de matriz
La solución anterior es muy simple y puede hacer el trabajo rápidamente cuando solo hay un par de criterios. Pero una fórmula SUMIF + SUMIF puede crecer enormemente si desea sumar valores con múltiples condiciones OR. En este caso, un mejor enfoque es utilizar un argumento de criterios de matriz en la función SUMIF. Examinemos ahora este enfoque.
Puede comenzar enumerando todas sus condiciones separadas por comas y luego encerrar la lista resultante separada por comas entre {corchetes}, que técnicamente se llama una matriz.
El argumento de matriz que consta de 3 valores obliga a su fórmula SUMIF a devolver tres resultados separados, pero como escribimos la fórmula en una sola celda, devolvería solo el primer resultado, es decir, el total de productos entregados por John . Para que este enfoque de criterios de matriz funcione, debe usar un pequeño truco más: incluya su fórmula SUMIF en una función SUM, como esta:
=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))
Como puede ver, un criterio de matriz hace que la fórmula sea mucho más compacta en comparación con SUMIF + SUMIF, y le permite agregar tantos valores como que le guste en la matriz.
Este enfoque funciona tanto con números como con valores de texto. Por ejemplo, si en lugar de los nombres de los proveedores en la columna C, tenía ID de proveedor como 1, 2, 3, etc. ., entonces su fórmula SUMIF sería similar a esto:
=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))
A diferencia de los valores de texto, los números no necesitan estar entre comillas dobles en Argumentos de matriz.
Ejemplo 3. SUMPRODUCT & SUMIF
En caso de que su forma preferida es enumerar los criterios en algunas celdas en lugar de especificarlos directamente en la fórmula, puede usar SUMIF junto con la función SUMPRODUCT que multiplica los componentes en los arreglos dados y devuelve la suma de esos productos.
=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))
Donde G2: G4 son las celdas que contienen sus criterios, los nombres de los proveedores en nuestro caso, como se ilustra en la captura de pantalla siguiente.
Pero, por supuesto, nada le impide enumerar los valores en una matriz de criterios de su función SUMIF si desea:
=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))
El resultado devuelto por ambas fórmulas será idéntico a lo que ves en la captura de pantalla:
Excel SUMIFS con múltiples criterios OR
Si desea sumar condicionalmente valores en Excel no simplemente con múltiples condiciones OR, sino con varios conjuntos de condiciones, tendrá que usar SUMIFS en lugar de SUMIF. Las fórmulas serán muy similares a lo que acabamos de discutir.
Como de costumbre, un ejemplo podría ayudar a ilustrar mejor el punto. En nuestra tabla de proveedores de frutas, agreguemos la Fecha de entrega (columna E) y encuentre la cantidad total entregada por Mike, John y Pete en octubre.
Ejemplo 1. SUMIFS + SUMIFS
La fórmula producida por este enfoque incluye mucha repetición y apariencia engorroso, pero es fácil de entender y, lo más importante, funciona:)
Ejemplo 2. SUM & SUMIFS con un argumento de matriz
He intentado explicar la esencia de este enfoque en el ejemplo SUMIF, por lo que ahora podemos simplemente copiar esa fórmula, cambiar el orden de los argumentos (como recordará, es diferente en SUMIF y SUMIFS) y agregar criterios adicionales. La fórmula resultante es más compacta que SUMIFS + SUMIFS:
=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))
El resultado devuelto por esta fórmula es exactamente el mismo que se ve en la captura de pantalla anterior.
Exampl e 3. SUMPRODUCT & SUMIFS
Como recordará, el enfoque SUMPRODUCT difiere de los dos anteriores en la forma en que ingresa cada uno de sus criterios en una celda separada en lugar de especificarlos directamente en la fórmula. En el caso de varios conjuntos de criterios, la función SUMPRODUCT no será suficiente y tendrá que emplear ISNUMBER y MATCH también.
Entonces, asumiendo que los nombres de los suministros están en las celdas H1: H3, la fecha de inicio está en la celda H4 y la fecha de finalización en la celda H5, nuestra fórmula SUMPRODUCT adopta la siguiente forma:
=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)
Mucha gente se pregunta por qué utilizar doble guión (-) en las fórmulas SUMPRODUCT. El caso es que SUMPRODUCT de Excel ignora todos los valores excepto los numéricos, mientras que los operadores de comparación de nuestra fórmula devuelven valores booleanos (VERDADERO / FALSO), que no son numéricos. Para convertir estos valores booleanos en 1 «sy 0» s, usa el signo menos doble, que técnicamente se llama operador unario doble. El primer unario coacciona VERDADERO / FALSO a -1/0, respectivamente. El segundo unario niega los valores, es decir, invierte el signo, convirtiéndolos en +1 y 0, que la función SUMPRODUCT puede entender.
Espero que la explicación anterior tenga sentido. E incluso si no es así, recuerde esta regla: use el operador unario doble (-) cuando esté usando operadores de comparación en sus fórmulas SUMPRODUCT.
Usando Excel SUM en fórmulas de matriz
Como recordará, Microsoft implementó la función SUMIFS en Excel 2007. Si alguien todavía usa Excel 2003, 2000 o anterior, tendrá que usar una fórmula de matriz SUM para agregar valores con múltiples criterios AND. Naturalmente, este enfoque también funciona en las versiones modernas de Excel 2013 – 2007, y puede considerarse una contraparte anticuada de la función SUMIFS.
En las fórmulas SUMIF discutidas anteriormente, ya ha utilizado argumentos de matriz, pero una fórmula de matriz es algo diferente.
Ejemplo 1. Suma con varios criterios AND en Excel 2003 y versiones anteriores
Regresemos al primer ejemplo en el que encontramos una suma de cantidades relacionadas a una fruta y un proveedor determinados:
Como ya sabe, esta tarea se realiza fácilmente utilizando un fórmula SUMIFS ordinaria:
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")
Y ahora, veamos cómo se puede realizar la misma tarea en las primeras versiones de Excel «sin SUMIFS» . En primer lugar, escriba todas las condiciones que deben cumplirse en la forma de rango = «condición». En este ejemplo, tenemos dos pares de rango / condición:
Condición 1: A2: A9 = «manzanas»
Condición 2: B2: B9 = «Pete»
Luego, escribe una fórmula SUM que «multiplica» todos los pares de rango / condición, cada uno entre paréntesis. El último multiplicador es el rango de la suma, C2: C9 en nuestro caso:
=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))
Como se ilustra en la captura de pantalla siguiente, la fórmula funciona perfectamente en la última versión de Excel 2013.
Ejemplo 2. Fórmulas de matriz SUM en las versiones modernas de Excel
Incluso en las versiones modernas de Excel, no se debe subestimar el poder de la función SUM. La fórmula de matriz SUM no es simplemente una gimnasia mental, sino que tiene un valor práctico, como se demuestra en el siguiente ejemplo.
Suponga , tienes dos columnas, B y C, y necesitas contar cuántas veces la columna C es mayor que la columna B, cuando un valor en la columna C es mayor o igual a 10. Una solución inmediata que te viene a la mente es usar la SUMA fórmula de matriz:
=SUM((C1:C10>=10) * (C1:C10>B1:B10))
No veo ninguna práctica aplicación a la fórmula anterior? Piénselo de otra manera:)
Suponga que tiene la lista de pedidos como se muestra en la captura de pantalla a continuación y desea saber cuántos productos no se han entregado en su totalidad en una fecha determinada. Traducido al lenguaje de Excel, tenemos las siguientes condiciones:
Condición 1: Un valor en la columna B (Elementos pedidos) es mayor que 0
Condición 2: Un valor en la columna C (Entregado) en menos que en la columna B
Condición 3: Una fecha en la columna D (Fecha de vencimiento) es menor que el 1/11/2014.
Poniendo los tres rangos / pares de condiciones juntos, obtienes la siguiente fórmula:
=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))
Bueno, los ejemplos de fórmulas discutidos en este tutorial solo han arañado la superficie de lo que las funciones SUMIFS y SUMIF de Excel realmente pueden hacer. Pero con suerte, te han ayudado a apuntar en la dirección correcta y ahora puedes sumar valores en tus libros de Excel sin importar cuántas condiciones intrincadas tienes que considerar.
- Cómo usar SUMIF en Excel: ejemplos de fórmulas para sumar celdas condicionalmente
- Cómo usar SUMIFS y SUMIF de Excel con varios criterios: ejemplos de fórmulas
- Cómo usar BUSCARV & funciones SUM o SUMIF en Excel
- SUMIF en Google Sheets con ejemplos de fórmulas
- COUNTIF en Excel – cuente si no en blanco, mayor que, duplicado o único
- Cómo contar y sumar celdas por color en Excel