Excel: evite os critérios SUMIF sobrepostos de contagem dupla de SUM

Question

Estou usando SUMIF like

=SUM(SUMIF(range, {C1, C2, ...}, values)) 

em que as strings em C1 e C2 contêm curingas. Por exemplo, C1 é "*A*" e C2 é "*B*" . Algumas células no intervalo correspondem a várias regras e são contadas duas vezes. Como posso evitar a contagem dupla desses valores para contá-los apenas uma vez?

Por exemplo, se a lista for {apple, banana, pear} e a pesquisa for {"p","a"} , recebo 3, contando apenas "pear" uma vez.

    

Answer 1

Eu realmente pensei que essa seria uma fórmula SUMPRODUCT(-- , mas não consigo fazer com que ela funcione. Isso deve funcionar embora -

=SUM(IF(FREQUENCY(IF(NOT(ISERR(SEARCH({"d","g"},A2:A10))),ROW(A2:A10)),ROW(A2:A10))>0,1))

Em que {"d","g"} será sua matriz de sequências de pesquisa.

É uma fórmula de matriz, então uma vez que você a coloca, você tem que pressionar ctrl + shft + entr e chaves devem aparecer na barra de fórmulas em torno de toda a função.

Observe que isso só funcionará se você pesquisar uma única coluna.

É possível que isso não funcione também, considerando curingas. Talvez regex seja o que você precisa?

    

Answer 2

É mais flexível ter seus critérios em células reais dentro da planilha, em vez de codificar na fórmula.

Se você usar um vertical , um intervalo contíguo de células (por exemplo, H1:H2 ) para este fim, e assumindo que Range é um vertical intervalo, você pode usar essa matriz de fórmulas ** :

=SUM(IF(MMULT(0+ISNUMBER(SEARCH(TRANSPOSE(Criteria),Range)),ROW(Criteria)^0)>0,Values))

Se você insiste em ter os critérios na fórmula, então:

=SUM(IF(MMULT(0+ISNUMBER(SEARCH({"p","a"},Range)),{1;1})>0,Values))

Atenciosamente