Re: Sumproduct(N and --

BobS wrote...
I use array formula quit often and was wondering if someone could please
explain the use of sumproduct(N and sumproduct(-- versus just sumproduct(.
I've seen the N and -- quit a bit in newsgroup responses, but am not clear
on their function. Thanks.

Both coerce boolean values to numeric values, TRUE to 1 and FALSE to 0.
This is necessary because SUMPRODUCT skips anything other than numbers.
For example,

=SUMPRODUCT({1;2;3;4}<=3,{1;10;100;1000})

returns 0 because this is equivalent to

=SUMPRODUCT({FALSE;FALSE;TRUE;TRUE},{1;10;100;1000})

and SUMPRODUCT skips all the items in the first argument. However,

=SUMPRODUCT(--({1;2;3;4}<=3),{1;10;100;1000})

returns 1100 because this is equivalent to

=SUMPRODUCT({0;0;1;1},{1;10;100;1000})

N({1;2;3;4}<=3) returns the same thing as --({1;2;3;4}<=3), but N
involves a function call, and that often bumps up against Excel limit
of 7 nested function calls. Also, N's semantics when passed array
arguments is strictly speaking undocumented, so it could change in
future (but that's unlikely).

.