Thank you all for the wonderful examples, especially Roger's that includes row and column.

When I started to learn SUMPRODUCT, I compared between double unary/comma and * and I found that * worked more times than double unary. Hence, I "prefer" to use *.

However, if I remember correctly, I was told that * is implied by the word "product" in SUMPRODUCT, so comma is preferred. I'll let Bob P. clarify.

All in all, the important thing is that when one operator doesn't work, I should try the others.

Hope the original poster doesn't feel distracted when I started this interesting discussion by mentioning the word "preference."

In case BobS is interested, here is another link with good info. http://mcgimpsey.com/excel/formulae/doubleneg.html



While I have got the experts' attention, I am going to start my own thread on SUMPRODUCT and CSE.



Epinn





"Roger Govier" wrote in message ...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
ups.com...
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).