Re: Sumproduct(N and --

Equally, if all the arrays are not in the same plane, the double unary
version fails, where the "*" version works

=SUMPRODUCT((A2:A4="b")*(B1:E1="Dept2")*(B2:E4))

=SUMPRODUCT(--(A2:A4="b"),--(B1:E1="Dept2"),--(B2:E4))

--
Regards

Roger Govier


"Biff" <biffinpitt@xxxxxxxxxxx> wrote in message
news:u3O4Lsl$GHA.4892@xxxxxxxxxxxxxxxxxxxxxxx
That having been said, (though for the life
of me I can't remember any examples), I have
also had cases where only x*x
would work as opposed to double unary,

Comparing a one dimensional array to a two dimensional array:

=SUMPRODUCT(--(A1:A5="x"),--(B1:C5="y"))
=SUMPRODUCT((A1:A5="x")*(B1:C5="y"))

The double unary version fails (#VALUE!)

Biff

"Ken Wright" <ken.wright@xxxxxxxxxxxxxxxxxx> wrote in message
news:B155C3BC-E211-4E02-B347-533B8BBA9BDB@xxxxxxxxxxxxxxxx
Not going to try and answer for Harlan, but it's not so much a
preference as
the right syntax for the job.

With the following data in A1:B5

abc def
1 2
3 4
2 3
4 5

Try each of the following formulas:-

=SUMPRODUCT(A1:A5*B1:B5)
=SUMPRODUCT(A1:A5,B1:B5)

First one fails, second one doesn't.

If you have text in any of your ranges (albeit even just the
headers), then
the x*x syntax will fall over. That having been said, (though for
the life
of me I can't remember any examples), I have also had cases where
only x*x
would work as opposed to double unary, so i wouldn't say it's just a
case of
preference.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------
It's easier to beg forgiveness than ask permission :-)



"Epinn" wrote:

Harlan,

I assume you meant >=3 (greater than or equal to 3) instead of <=3
in your example.

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

In case anyone is interested, I just want to say that the above
formula is equivalent to

=SUMPRODUCT(({1;2;3;4}>=3)*{1;10;100;1000})

Sorry, Bob P., I know your preference is double unary and comma.
Wonder if Harlan has a preference.

Epinn


"Harlan Grove" <hrlngrv@xxxxxxx> wrote in message
news:1162420089.576103.46050@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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).







.