Resumen
En ciertos casos, puede utilizar SUMIFS como una fórmula de búsqueda para recuperar un valor numérico. En el ejemplo que se muestra, la fórmula en G6 es:
=SUMIFS(sales,region,G4,quarter,G5)
donde región (B5: B20), trimestre (C5: C20) y ventas (D5: D20) son rangos con nombre.
El resultado son las ventas del tercer trimestre para la región Central, 127.250.
Explicación
Si es nuevo en la función SUMIFS, puede encontrar una descripción básica con muchos ejemplos aquí.
La función SUMIFS está diseñada para sumar valores numéricos según uno o más criterios. Sin embargo, en casos específicos, es posible que pueda utilizar SUMIFS para "buscar" un valor numérico que cumpla con los criterios requeridos. Las principales razones para hacer esto son la simplicidad y la rapidez.
En el ejemplo que se muestra, tenemos datos de ventas trimestrales para cuatro regiones. Comenzamos dando a SUMIFS un rango de suma y la primera condición, que prueba la región para el valor en G4, "Central":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- El rango de suma son las ventas (D5: D20)
- El rango de criterios 1 es la región (B5: B20)
- El criterio 1 es G4 ("Central")
Luego agregamos el segundo par rango / criterio, que verifica un cuarto:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- El rango de criterios 2 es un cuarto (C5: C20)
- El criterio 2 es G5 ("Q3")
Con estos criterios, SUMIFS devuelve 127,250, el número de ventas del tercer trimestre central.
El comportamiento de SUMIFS es sumar todos los valores coincidentes. Sin embargo, debido a que solo hay un valor coincidente, el resultado es el mismo que el valor en sí.
A continuación, analizamos varias opciones de fórmulas de búsqueda.
Opciones de fórmula de búsqueda
En esta sección se revisan brevemente otras opciones de fórmulas que producen el mismo resultado. Con la excepción de SUMPRODUCT (en la parte inferior), estas son fórmulas de búsqueda más tradicionales que ubican la posición del valor objetivo y devuelven el valor en esa ubicación.
Con VLOOKUP
Desafortunadamente, BUSCARV no es una buena solución a este problema. Con una columna auxiliar, es posible crear una fórmula VLOOKUP para que coincida con varios criterios (ejemplo aquí), pero es un proceso incómodo que requiere que modifique los datos de origen.
Con INDICE y MATCH
INDEX y MATCH es una combinación de búsqueda muy flexible que se puede utilizar para todo tipo de problemas de búsqueda, y este ejemplo no es una excepción. Con INDEX y MATCH, podemos buscar ventas por región y trimestre con una fórmula de matriz como esta:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
El truco con este enfoque es usar lógica booleana con operaciones de matriz dentro de la función COINCIDIR para construir una matriz de 1 y 0 como la matriz de búsqueda. Entonces podemos pedirle a la función COINCIDIR que encuentre el número 1. Una vez que se crea la matriz de búsqueda, la fórmula se resuelve como:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Con solo 1 restante en la matriz de búsqueda, COINCIDIR devuelve una posición de 11 a la función INDICE, e INDICE devuelve el número de ventas en esa posición, 127,250.
Para obtener más detalles, consulte: INDICE y COINCIDIR con varios criterios
Con XLOOKUP
XLOOKUP es una nueva función flexible en Excel que puede manejar matrices de forma nativa. Con XLOOKUP, podemos usar exactamente el mismo enfoque que con INDEX y MATCH, usando lógica booleana y operaciones de matriz para crear una matriz de búsqueda:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Una vez que se han ejecutado las operaciones de matriz, la fórmula se resuelve como:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
Y XLOOKUP devuelve el mismo resultado que el anterior, 127,250.
Más: XLOOKUP con múltiples criterios
Con LOOKUP
La función BÚSQUEDA es una función anterior en Excel que muchas personas ni siquiera conocen. Uno de los puntos fuertes de LOOKUP es que puede manejar matrices de forma nativa. Sin embargo, LOOKUP tiene algunas debilidades distintas:
- No se puede bloquear en "modo de coincidencia exacta"
- Siempre asume que los datos de búsqueda están ordenados, AZ
- Siempre devuelve una coincidencia aproximada (si no se puede encontrar una coincidencia exacta)
No obstante, LOOKUP se puede usar para resolver este problema de la siguiente manera:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
que se simplifica a:
=LOOKUP(2,(#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!),sales)
If you look closely, you can see a single number 1 in a sea of #DIV/0! errors. This represents the value we want to retrieve.
We use a lookup value of 2 because we can't guarantee the array is sorted. So, we force all non-matching rows to errors, and ask LOOKUP to find a 2. LOOKUP ignores the errors and dutifully scans the entire array looking for 2. When the number 2 can't be found, LOOKUP "backs up" and matches the last non-error value, which is the 1 in the 11th position. The result is the same as above, 127,250.
More detailed explanation here.
With SUMPRODUCT
As usual, you can also use the Swiss Army Knife SUMPRODUCT function to solve this problem as well. The trick is to use boolean logic and array operations to "zero out" all but the one value we want:
=SUMPRODUCT(sales*((region=G4)*(quarter=G5)))
After the array math inside SUMPRODUCT is complete, the formula simplifies to:
=SUMPRODUCT((0;0;0;0;0;0;0;0;0;0;127250;0;0;0;0;0))
This is technically not really a lookup formula, but it behaves like one. With just a single array to process, the SUMPRODUCT function returns the sum of the array, 12,7250.
See this example for a more complete explanation.
In spirit, the SUMPRODUCT option is closest to the SUMIFS formula since we are summing values based on multiple criteria. As before, it works fine as long as there is only one matching result.
Summary
SUMIF can indeed be used like a lookup formula, and configuration may be simpler than a more conventional lookup formula. In addition, if you are working with a large data set, SUMIFS will be a very fast option. However, you must keep in mind two key requirements:
- The result must be numeric data
- Criteria must match only one result
Si la situación no cumple con ambos requisitos, SUMIFS no es una buena opción.
