Tengo una colección de cadenas de texto y cada una tiene un valor numérico asociado. Necesito sumar los valores numéricos asociados para aquellas entradas que "califican". Una entrada de texto califica por contener una o más cadenas de destino designadas. Potencialmente, una entrada puede contener varias cadenas de destino o una cadena de destino más de una vez. Sin embargo, solo quiero sumar el valor asociado una vez para la entrada si la entrada califica por contener alguna coincidencia con cualquiera de los objetivos o combinaciones de objetivos.
Por ejemplo, digamos que las celdas A1:A3 contienen respectivamente apple, banana, peary B1:B3 contienen cada una el número 1. Mis objetivos de búsqueda son ay p. Las tres entradas de texto califican porque cada una contiene al menos una instancia de al menos uno de los objetivos. La suma de los valores asociados en la columna B debería arrojar un resultado de 3.
Intenté esto usando objetivos SUMIF y comodines. Mi fórmula para este ejemplo es:
=SUM(SUMIF(A:A, {"*a*", "*p*"}, B:B))
Sin embargo, esto cuenta dos veces las entradas que coinciden con más de un objetivo. En este caso, los tres contienen ay dos también contienen p, por lo que produce una suma de 5.
¿Cómo puedo lograr esto sin contar dos veces las entradas?
Respuesta1
Es más flexible tener sus criterios en celdas reales dentro de la hoja de trabajo, en lugar de codificarlos en una fórmula.
Si usas unvertical, rango contiguo de celdas (p. ej. H1:H2) para este fin, y suponiendo queRangoes unverticalrango, puedes usar estofórmula matricial**:
=SUM(IF(MMULT(0+ISNUMBER(SEARCH(TRANSPOSE(Criteria),Range)),ROW(Criteria)^0)>0,Values))
Si insiste en tener los criterios dentro de la fórmula, entonces:
=SUM(IF(MMULT(0+ISNUMBER(SEARCH({"p","a"},Range)),{1;1})>0,Values))
Saludos
Respuesta2
Aquí tienes una solución relativamente sencilla. Con los valores asociados all 1, produce el resultado deseado de 3, pero asigné valores diferentes para demostrar que se seleccionaron los valores correctos e incluí una entrada que no coincide por si acaso.
La lista de entradas está en la columna C y sus valores asociados en la columna D. El resultado está en E1.
Solo ciertas funciones pueden usar comodines, por lo que se usa BÚSQUEDA para la cadena de destino.
El método típico para manejar múltiples criterios de quirófano es agregar los resultados de cada prueba. Sin embargo, eso cuenta dos veces cuando los artículos pueden cumplir múltiples criterios. Para solucionar eso, se comprueban las pruebas de criterios agregados para ver si la suma es mayor que cero, y eso es lo que se utiliza con el valor asociado.
Manejar las cadenas de búsqueda de destino como una matriz se vuelve complicado, porque las funciones comunes utilizadas para este tipo de fórmulas calculan un resultado para toda la matriz antes de aplicarlo al siguiente término. Así que traté cada criterio por separado. Para obtener más criterios, simplemente agregue otro ISNUMBER(SEARCH("target",range))para cada uno dentro del paréntesis antes de la >0prueba.
SUMPRODUCT realiza los cálculos de estilo de matriz con una fórmula normal que no es de matriz.
La fórmula en E1 es:
=SUMPRODUCT(((ISNUMBER(SEARCH("p",C1:C4))+ISNUMBER(SEARCH("a",C1:C4)))>0)*D1:D4)
Respuesta3
Realmente pensé que esto sería una SUMPRODUCT(--fórmula, pero no puedo hacer que funcione. Sin embargo, esto debería funcionar.
=SUM(IF(FREQUENCY(IF(NOT(ISERR(SEARCH({"d","g"},A2:A10))),ROW(A2:A10)),ROW(A2:A10))>0,1))
Donde {"d","g"}estará su conjunto de cadenas de búsqueda.
Es una fórmula matricial, por lo que una vez que la ingresas, debes presionar ctrl+ shft+ entry las llaves deberían aparecer en la barra de fórmulas alrededor de toda la función.
Tenga en cuenta que solo funcionará si busca en una sola columna.
Es posible que esto no funcione tan bien, considerando los comodines. ¿Quizás expresiones regulares es lo que necesitas?