SUMIFS con OR

De todas las funciones introducidas en Excel 2007, 2010 y 2013, mi favorito personal es SUMIFS. La función SUMIFS realiza una suma de múltiples condiciones. La función está diseñada con lógica AND, pero existen varias técnicas que nos permiten utilizar la lógica OR en su lugar. Esta publicación explora algunos de ellos.

Nota: si su versión de Excel tiene la función FILTRO, consulte también esta publicación.

Objetivo

Seamos claros sobre nuestro objetivo echando un vistazo a una hoja de trabajo. Supongamos que estamos intentando generar un pequeño informe basado en datos exportados desde un sistema contable. Nos gustaría escribir fórmulas para completar el informe que se muestra a continuación.

Queremos completar el informe agregando los valores de los datos contables exportados, que se muestran a continuación.

Dado que la función SUMIFS está diseñada con lógica AND, la siguiente fórmula no funcionaría para completar el valor de ventas del informe:

=SUMARSIFS($C$18:$C$28,$B$18:$B$28,"Ventas-Mano de Obra",$B$18:$B$28,"Ventas-Hardware",$B$18:$B$28," Software-Ventas")

La fórmula no funcionaría porque no hay filas donde la cuenta sea igual a Ventas-Mano de obra E igual a Ventas-Hardware E igual a Ventas-Software. Como resultado, la fórmula anterior devuelve cero.

Ahora que tenemos una idea clara de la situación, analizamos varios enfoques.

SUMIFS y comodín

Si tenemos suerte, hay un patrón uniforme en las etiquetas que podemos usar con comodines. Por ejemplo, en los datos anteriores, todas las cuentas de ventas comienzan con “Ventas-” y eso nos hace tener mucha suerte. Simplemente podemos usar un comodín para indicarle a la función SUMIFS que sume cualquier fila donde la cuenta comience con Ventas, como se muestra en la siguiente fórmula:

=SUMIFS($C$18:$C$28,$B$18:$B$28,"Ventas-*")

Aquí está en el informe:

Nota: Alternativamente, podríamos hacer referencia a la etiqueta del informe en la celda y actualizar el tercer argumento a B6”*”

Una función similar podría completar el valor del informe COS, de la siguiente manera:

=SUMIFS($C$18:$C$28,$B$18:$B$28,"COS-*")

Y luego podríamos completar el valor del informe de gastos de venta, generales y administrativos sumando todas las cuentas que no tienen un guión, de la siguiente manera:

=SUMARSIFS($C$18:$C$28,$B$18:$B$28,"*-*")

Sin embargo, en la práctica, no solo tenemos la suerte de tener ese patrón. Entonces, exploramos otras opciones.

SUMIFS+SUMIFS

Otra opción es simplemente encadenar una serie de funciones SUMIFS, cada una diseñada para agregar una etiqueta específica. Por ejemplo, podríamos usar SUMIFS para sumar todas las filas donde la cuenta es igual a Ventas-Mano de obra y luego agregar a su resultado una función SUMIFS diseñada para sumar todas las filas donde la cuenta es igual a Ventas-Hardware, y así sucesivamente . Esta idea se puede ilustrar de la siguiente manera:

=SUMIFS()+SUMIFS()+SUMIFS()

La fórmula correspondiente para completar el valor del informe de ventas se muestra a continuación.

=SUMIFS($C$18:$C$28,$B$18:$B$28,"Ventas-Mano de obra")+SUMIFS($C$18:$C$28,$B$18:$B$28,"Ventas-Hardware ")+SUMIFS($C$18:$C$28,$B$18:$B$28,"Software-Ventas")

Cuando sólo hay un par de valores de criterios, este enfoque puede hacer el trabajo. Cuando hay varios valores de criterios, podemos simplificar la fórmula y eliminar la redundancia utilizando un argumento de valor de criterios de matriz. Examinamos este enfoque ahora.

SUMA(SUMARSIFS({}))

Podemos crear una lista de valores de criterios e incluirlos en un único argumento SUMIFS. Para crear una lista como esta, técnicamente llamada matriz, simplemente la rodeamos con {llaves}. Hay un pequeño truco para que este enfoque funcione, pero vayamos paso a paso.

Primero, echamos un vistazo a una fórmula que incluye la lista de cuentas en un argumento de matriz:

=SUMIFS($C$18:$C$28,$B$18:$B$28,{"Ventas-Mano de obra","Ventas-Hardware","Ventas-Software"})

Notarás que usarás una función SUMIFS con un único argumento de valor de criterio. El argumento del valor de los criterios es una lista separada por comas rodeadas por {llaves}. Básicamente, esto indica la función para devolver los montos de cada una de las cuentas enumeradas.

Sin embargo, cuando presionamos Enter, notamos que la fórmula solo devuelve la suma de la primera cuenta, Ventas-Mano de obra. Esto nos lleva a nuestro pequeño truco. El argumento de matriz hace que la fórmula devuelva tres resultados y, como escribimos la fórmula en una sola celda, solo vemos el primer resultado. Para que nuestra celda muestre la suma de los tres resultados, simplemente encerramos la función SUMIFS en una función SUMA, de la siguiente manera:

=SUM(SUMARSI($C$18:$C$28,$B$18:$B$28,{"Ventas-Mano de obra","Ventas-Hardware","Ventas-Software"}))

Nota: si está familiarizado con las fórmulas matriciales, puede seleccionar tres celdas y luego presionar Ctrl+Entrar para ver los tres resultados en tres celdas.

Si bien cada uno de los enfoques anteriores puede lograr nuestro objetivo, existe otro enfoque que puede funcionar mejor para los libros de uso recurrente. La preocupación con todas las opciones anteriores es que almacenan los valores de los criterios, los nombres de las cuentas, en las celdas como cadenas de texto. Esto nos impide utilizar fórmulas consistentes dentro del rango del informe. Siempre que sea posible, intentamos escribir fórmulas consistentes dentro de un rango que se pueda completar. No podemos completar las fórmulas anteriores porque los valores de los criterios están integrados en ellas. Dado que esto hace que la hoja de cálculo sea más difícil de mantener con el tiempo, a menudo se prefiere almacenar los valores de los criterios en celdas y luego hacer referencia a las celdas en la función SUMIFS. Podemos lograr esto utilizando una tabla de búsqueda de grupos de informes simple.

Informe de búsqueda de grupo

Antes de profundizar en la mecánica, retrocedemos un momento. Básicamente, estamos intentando agrupar los valores de las transacciones para fines de generación de informes, pero las etiquetas de grupo no aparecen en los datos. Si el sistema de contabilidad hubiera exportado las etiquetas de los grupos de informes, nuestro trabajo habría sido fácil. La buena noticia es que, aunque no fue así, podemos definir fácilmente los grupos de informes con una simple tabla de búsqueda.

Considere la siguiente tabla de búsqueda que define los grupos de informes deseados.

Podemos agregar fácilmente una columna auxiliar a la tabla de datos que recupere el grupo de informes adecuado. Podemos utilizar nuestra función de búsqueda favorita para realizar esta tarea. Por ejemplo, la siguiente fórmula recupera el grupo de informes con una función BUSCARV:

=BUSCARV(B18,$B$34:$C$44,2,0)

Cuando completamos la fórmula, la tabla de datos actualizada incluye el grupo de informes, como se muestra a continuación.

Ahora podemos completar el valor de ventas del informe con una fórmula simple.

=SUMARSIFS($C$18:$C$28,$D$18:$D$28,B6)

Podemos completar esta fórmula a lo largo del informe como se muestra a continuación.

Las fórmulas coherentes nos alegran mucho y la columna del grupo de informes deja claro cómo fluyen los valores en el informe.

Si tiene alguna otra técnica para usar la lógica OR con SUMIFS, compártala publicando un comentario a continuación… ¡gracias!

Recursos adicionales

• Descargue el archivo de muestra: SUMIFS con OR
• La función SUMPRODUCT también se puede utilizar para realizar suma condicional con lógica OR
• Si necesita usar la lógica OR para numerosas columnas, en lugar de dentro de una sola columna como se mencionó anteriormente, tenga cuidado de no contar dos veces las filas que cumplen ambos criterios.
• Excel University Volumen 2 explora las funciones SUMIFS y BUSCARV en detalle