Un problema que surge mucho en Excel es contar o sumar en función de múltiples condiciones de quirófano. Por ejemplo, ¿tal vez necesite analizar datos y contar pedidos en Seattle o Denver, para artículos que son rojos, azules o verdes? Esto puede ser sorprendentemente complicado, ¡así que naturalmente es un buen desafío!
El reto
Los datos a continuación representan pedidos, un pedido por fila. Hay tres desafíos separados.
¿Qué fórmulas en F9, G9 y H9 contarán correctamente los pedidos con las siguientes condiciones:
- F9 - Camiseta o sudadera con capucha
- G9 - (camiseta o sudadera con capucha) y (rojo, azul o verde)
- H9 - (camiseta o sudadera con capucha) y (rojo, azul o verde) y (Denver o Seattle)
El sombreado verde se aplica con formato condicional e indica valores coincidentes para cada conjunto de criterios OR en cada columna.
Para su comodidad, están disponibles los siguientes rangos con nombre:
artículo = B3: B16
color = C3: C16
ciudad = D3: D16
Se adjunta la hoja de trabajo. ¡Deje sus respuestas a continuación como comentarios!
Respuesta (haga clic para expandir)Mi solución usa SUMPRODUCT con ISNUMBER y MATCH de esta manera:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Que contará los pedidos donde …
- El artículo es (camiseta o sudadera con capucha) y
- El color es (rojo, azul o verde) y
- La ciudad es (Denver o Seattle)
Varias personas también sugirieron el mismo enfoque. Me gusta esta estructura porque se escala fácilmente para manejar más criterios y también funciona con referencias de celda (en lugar de valores codificados). Con referencias de celda, la fórmula en H9 es:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
La clave de esta fórmula es la construcción ISNUMBER + MATCH. MATCH se configura "al revés": los valores de búsqueda provienen de los datos y se utilizan criterios para la matriz. El resultado es una matriz de una sola columna cada vez que se usa MATCH. Esta matriz contiene errores # N / A (sin coincidencia) o números (coincidencia), por lo que ISNUMBER se usa para convertir a los valores booleanos TRUE y FALSE. La operación de multiplicar los arreglos coacciona los valores VERDADERO FALSO a 1 y 0, y el arreglo final dentro de SUMPRODUCTO contiene 1 donde las filas cumplen los criterios. SUMPRODUCT luego suma la matriz y devuelve el resultado.
