CODEWITHSUNDEEP
excel
user2591854
user2591854

Reputation: 302

SUMPRODUCT coercion in Excel

Given the following Excel sheet:

A1 = 10
A2 = 20
A3 = 30

I know / understand why SUMPRODUCT( 0+(A1:A3>0) ) returns 3 (the 3 boolean values are being coerced in to integers due to the addition).

Why does SUMPRODUCT( 1+(A1:A3>0) ) return 6? (and increasing the constant gives us a result of Constant*N + N.

Is this a documented feature of Excel, or just some weird oddity that I've stumbled across?

For the sake of clarity, these formulas are not array formulas, and Im using Excel 2003.

Upvotes: 0

Views: 894

Answers (1)

barry houdini
barry houdini

Reputation: 46331

That formula returns 6 because in your example A1:A3 are all > 0 so A1:A3>0 returns this array

{TRUE;TRUE;TRUE} and when you do some sort of calculation on TRUE/FALSE values TRUE becomes 1 and FALSE becomes zero so with

1+{TRUE;TRUE;TRUE}

you get

{2;2;2}

which SUMPRODUCT sums to get 6

You'd get the same result with SUM but "array entered" i.e.

=SUM(1+(A1:A3>0))

confirmed with CTRL+SHIFT+ENTER

the logic and result is the same in both

The constant 1 is always added to every element in the array - that sort of "array math" is standard in excel

Upvotes: 4

Related Questions