Usando a condição OR em SUMPRODUCT

Question

Eu tenho três colunas de dados - uma coluna de soma ( A ) e, em seguida, as duas colunas que formam a soma ( B e C ). Às vezes, apenas uma das colunas B e C é preenchida; ocasionalmente, as colunas B e C estão em branco.

Eu quero a coluna SUM A (ou seja, um total dos totais), somente quando houver dados nas colunas B e / ou C . Então, eu esperava que o seguinte funcionasse:

=SUMPRODUCT(--((B2:B10<>"")+(C2:C20<>"")),A2:A10)

Mas o problema é que, se houver dados em ambas as colunas B e C, a matriz formada a partir de (B2:B10<>"")+(C2:C10<>"") será 2 e a anterior -- não a converterá em TRUE (por exemplo, {1,1,0,1,0,1,1,1,0} + {1,0,1,1,0,1,1,0,1} = {2,1,1,2,0,2,2,1,1} ) e esse arranjo final é então multiplicado com os valores na coluna A , dobrando os valores 1, 4, 6 e 7.

Isso parece um comportamento estranho! Como faço para se comportar corretamente?!

    

Answer 1

Você poderia fazer algo assim:

=SUMPRODUCT($A$2:$A$11*($B$2:$B$11&$C$2:$C$11<>""))

A ideia é que a concatenação de B e C esteja em branco apenas se ambos estiverem em branco.

    

Answer 2

Aha - Eu precisava da função SIGN :

=SUMPRODUCT(A2:A10,SIGN((B2:B10<>"")+(C2:C10<>"")))

Ainda acho estranho que, no que diz respeito ao SUMPRODUCT, TRUE + TRUE = 2. E que 2 não apenas avalie como TRUE quando -- está em jogo.

    

Answer 3

Acho que você pode considerar =SUMIFS() function.

Sua condição pode ser reescrita como:

• Condição 1: Coluna B é preenchida e Coluna C está em branco; OU
• Condição 2: a coluna B está em branco e a coluna C é preenchida; OU
• Condição 3: Coluna B e Coluna C são preenchidas.

Como as 3 condições são mutuamente exclusivas, o SUMPRODUCT complicado é essencialmente =SUMIF(Condition 1)+SUMIF(Condition 2)+SUMIF(Condition 3) . Reescrito como SUMIFS :

=SUMIFS(A2:A10,B2:B10,"<>",C2:C10,"")+SUMIFS(A2:A10,B2:B10,"",C2:C10,"<>")+SUMIFS(A2:A10,B2:B10,"<>",C2:C10,"<>")

Em termos de desempenho, essa solução pode ser melhor do que usar SUMPRODUCT também se houver um grande número de linhas para resumir.