Search code examples
excelexcel-formulasumproduct

Using SUMPRODUCT involving a column which contains empty values coming from an IF(...; ...; "")

Let's say we have this Excel sheet:

  • A column containing 1 ; 2 ; 3 (i.e. only 3 rows)
  • B column containing =IF(COUNT(A1)>0;1/A1^2;"") recopied in B1:B100. Therefore only 3 cells will be non-empty, and the remaining ones won't contain #DIV/0!: they will be empty thanks to the IF COUNT > 0 condition
  • another cell containing =SUMPRODUCT(A1:A100;B1:B100): it works: 1.8333333...
  • another cell containing =SUMPRODUCT(A1:A100;B1:B100 - 5): it doesn't work: since the B column contains a mix of empty cells and numbers, B1:B100 - 5 isn't understood.

In this last example, how to compute the sum of the products a_i * (b_i - 5), for each non-empty row, with SUMPRODUCT?

I also tried with N(...) without success.

Solution

  • If you did want to try and use N(), there is a trick to it. You have to put

    =SUMPRODUCT(A1:A100,N(+B1:B100)-5)

    Don't ask me why.