¿cómo utilizar sumif/sumifs con lógica or?
A veces necesitamos hacer pequeños cambios en las fórmulas para que sean efectivas.
Así de simple, SUMAR.O. Es una fórmula avanzada que te ayuda a aumentar la flexibilidad de SUMIF. Sí, también puedes hacer SUMIFS. Pero antes de usarlo, déjame decirte una cosa sobre SUMIF.
En SUMIFS solo puedes usar un criterio y en SUMIFS puedes usar múltiples criterios para obtener una suma.
Sólo una cosa así. Digamos que en SUMIFS, si especifica dos criterios diferentes, solo se sumarán las celdas que cumplan ambos criterios.
Debido a que funciona con lógica AND, se deben cumplir todos los criterios para que se incluya una celda. Cuando combinas la lógica OR con SUMIF/SUMIFS, puedes agregar valores usando dos criterios diferentes al mismo tiempo.
Sin más preámbulos, aprendamos esta asombrosa fórmula.
¿Realmente necesitamos lógica OR? ¿Un ejemplo?
Permítanme mostrarles un ejemplo en el que necesitamos agregar lógica OR en SUMIFS o SUMIF.
Echa un vistazo a la tabla de datos a continuación donde tienes una lista de productos con su cantidad y estado de envío actual.
En estos datos tienes dos tipos de productos (defectuosos y dañados) que debes devolver a nuestro proveedor.
Pero antes de eso, desea calcular la cantidad total de ambos tipos de productos. Si eliges el método normal, debes aplicar SUMAR.SI dos veces para obtener este total.
Pero si usas una condición OR en SUMAR.SI, puedes obtener el total de ambos productos con una sola fórmula.
OK, apliquemos OR con SUMIFS
Antes de comenzar, descargue este archivo de muestra desde aquí continuará.
En cualquier otra celda de su hoja de cálculo donde desee calcular el total, inserte la fórmula a continuación y presione Entrar.
=SUM(SUMIFS(C2:C21,B2:B21,{"Damage","Faulty"}))
En la fórmula anterior ha utilizado SUMIFS pero si desea utilizar SUMIFS, puede insertar la siguiente fórmula en la celda.
=SUM(SUMIF(B2:B21,{"Damage","Faulty"},C2:C21))
Usando las dos fórmulas anteriores obtendrás 540 en el resultado. Para volver a verificar, simplemente verifique el total manualmente.
¿Cómo funciona esta fórmula?
Como dije, la función SUMIFS usa la lógica AND para sumar los valores. Pero la fórmula que usó anteriormente incluye lógica OR.
Para entender esta fórmula, es necesario dividirla en tres partes diferentes.
- Lo primero es entender que has utilizado dos criterios diferentes en esta fórmula utilizando el concepto de matriz. Obtenga más información sobre el tablero desde aquí .
- Lo segundo es que cuando especificas dos valores diferentes usando una matriz, SUMIFS debe buscar los dos valores por separado.
- La tercera cosa es que incluso después de usar una fórmula matricial, SUMIFS no puede devolver la suma de los dos valores en una sola celda. Por eso es necesario unirlo a la función SUMA.
Con el concepto anterior, puedes obtener un total para ambos tipos de productos en una sola celda.
Esto funcionará igual con SUMIF y SUMIFS.
Y también puede ampliar su fórmula especificando el nombre del producto en el segundo criterio en SUMIFS para obtener el total de un solo producto.
Obtener el archivo Excel
Usando rango de criterios dinámicos
En la sección de comentarios, alguien me pidió que usara una referencia de celda para agregar múltiples criterios en lugar de insertarlos directamente en la fórmula.
Bueno, esa es una pregunta súper válida y puedes hacerlo usando un rango dinámico con nombre en lugar de valores estrictos.
Y para ello, sólo necesitas hacer dos pequeños cambios en tu fórmula.
- La primera es que en lugar de usar llaves, debes usar un rango con nombre (la mejor manera es usar una matriz) de tu valor.
- Y después de eso, debe ingresar esta fórmula usando Ctrl+Shift+Enter como fórmula matricial adecuada.
Entonces ahora tu fórmula será:
Múltiples criterios con diferentes columnas.
Déjame llevarte al siguiente nivel de esta fórmula. Basta con mirar la tabla de datos a continuación.
En esta tabla tiene dos columnas diferentes con dos tipos de estado diferentes. Ahora, a partir de esta tabla de datos, debe sumar la cantidad en la que el producto está dañado; el estado de devolución es «No».
Esto sólo se refiere a productos dañados y defectuosos que aún no han sido devueltos. Y la fórmula para esto será.
=SUM(SUMIFS(D2:D21,B2:B21,{"Damage","Faulty"},C2:C21,"No"))
Obtener el archivo Excel
Conclusión
La mejor parte de utilizar la técnica OR es que puedes agregarle muchos criterios. Si desea utilizar sólo la lógica OR, puede utilizar SUMAR.SI. Y si desea crear una condición OR y una condición AND en una sola fórmula, puede usar SUMIFS para eso.
Sabes, este es uno de mis consejos de fórmulas favoritos y espero que te haya resultado útil.
Ahora dime una cosa. ¿Habías usado alguna vez este tipo de fórmula? Comparte tus pensamientos conmigo en la sección de comentarios, me encantaría saber de ti y no olvides compartir este consejo con tus amigos.