Experts,

I have a question and I am starting my own thread. The subject is SUMPRODUCT- double unary vs. *

Please help.

Epinn

"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.