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

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.