O Sumproduct falha quando o intervalo é de apenas 1 linha e parte do cálculo está em branco

Question

Eu tenho a seguinte fórmula que funciona quando os intervalos têm mais de 1 linha, mas gera um erro de #VALUE se houver apenas 1 linha e um dos fatores estiver em branco.

=SUMPRODUCT(cost, units)

Cost    Units
   1         1
   2         1

O resultado é 3.

Cost    Units
   1         
   2         

O resultado é 0.

Cost    Units
   1         1

O resultado é 1.

Cost    Units
   1         

O resultado é #VALUE .

Eu posso evitar o erro envolvendo a fórmula em um IFERROR:

=IFERROR((SUMPRODUCT(cost, units)), 0)

Por que o SUMPRODUCT não funciona com apenas uma linha? Existe uma maneira melhor de lidar com isso do que o wrapper IFERROR?

    

Answer 1

Tente

=SUMPRODUCT(costs*units)

É interessante ver na Ferramenta Avaliar Fórmula que a função passa por este estágio:

e, em seguida, apresenta o #Value! erro. Mas se uma célula contiver a fórmula atual

=SUMPRODUCT(1,0)

então o resultado é 0. Isso parece um bug.

Outra sintaxe possível que não apresenta um erro é

=SUMPRODUCT(--(costs),--(units))

Editar: Depois de mais pesquisas e opiniões de colegas MVPs do Excel, aqui está uma explicação para o comportamento descrito acima: dentro do Sumproduct, o Excel coage as células vazias em valores zero, MAS somente se for um array , ou seja, um intervalo que consiste em mais de uma célula. Um único intervalo de células não aciona a coerção e, portanto, a célula vazia não é coagida a 0.

O uso do operador de multiplicação ou do duplo unário também forçará a célula vazia a um valor zero.

É confuso e levemente irritante, mas se a entrada for uma única célula vazia que não esteja conectada com operadores ou dupla unária, a única célula vazia será obtida pelo valor de face e ativará o valor #Value! erro, já que não é numérico.

    

Answer 2

Na página de documentação do MS

Remarks

• The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
• SUMPRODUCT treats array entries that are not numeric as if they were zeros.