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.

Everything you always wanted to know about Sumproduct but didn't know where
to look:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Biff

"BobS" wrote in message
...
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.

Here's an explanation of the Double Minus Sign (--)

When you use a Boolean formula (a formula that returns TRUE or FALSE), Excel
returns those values. If the formula returns "numeric text", Excel will
treat it AS text. To coerce the conversion from Boolean to Numeric, or from
"numeric text" to Numeric, you need to apply an arithmetic operator. The
generally accepted convention is to use a double minus sign (--).

It works this way:
The negative of a value reverses the sign.
The negative of that value restores the sign.

Example:
RIGHT("W1000",4) returns with the *word* "1000"
-RIGHT("W1000",4) converts "1000" to the number -1000
--RIGHT("W1000",4) converts the negative number to 1000

In the case of boolean values, the dbl-neg converts TRUE and FALSE to 1 and
0, respectively.

You could achieve the same results by multiplying a value by 1, but the
dbl-neg indicates to knowledgable users that a "type conversion" is being
effected.


I'm not sure what you mean by SUMPRODUCT(N. Can you give an example?

Does that help?
***********
Regards,
Ron

XL2002, WinXP


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

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

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

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

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

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

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

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.