SUMPRODUCT question

[Lee Harris]

Where does one find out in detail about this and other Excel functions
outside Excels own help

for example, I often see formulas such as

=SUMPRODUCT(--(A1:A10=B1:B10),(C1:C10))

quoted, and one guy helped me out with this on a sheet of mine, but I have
no idea where the two "-" signs came from, how they work or how you find out
about these. Is this array arithmetic? If so, where is that detailed in the
help, or on the web?

thanks in advance
LH

 

[Bruno Campanini]

Where does one find out in detail about this and other Excel functions
outside Excels own help

for example, I often see formulas such as

=SUMPRODUCT(--(A1:A10=B1:B10),(C1:C10))

quoted, and one guy helped me out with this on a sheet of mine, but I have
no idea where the two "-" signs came from, how they work or how you find
out about these. Is this array arithmetic? If so, where is that detailed
in the help, or on the web?

thanks in advance
LH

See this site:
www.xldynamic.com/source/xld.SUMPRODUCT.html

Bruno

 

[joeu2004]

Where does one find out in detail about this and
other Excel functions outside Excels own help
for example, I often see formulas such as
=SUMPRODUCT(--(A1:A10=B1:B10),(C1:C10))
[....]
where is that detailed in the help, or on the web?

I am so-o glad you asked this question. I have been
meaning to post a similar question for a long time,
but I keep forgetting.

Someone posted an answer for SUMPRODUCT per se. That
is great. Thanks.

But I would like to know the answer to the more
general question: where to find all such tricks,
which seem to be undocumented? At least, I do not
see them on the standard Help page.

For example, I undertstand the tricks used in the
SUMPRODUCT example above. But I cannot apply the
same trick (an operation on an entire range) in some
other functions, although I believe I have seen the
tricks used by others in some other functions.

Bottom line: how do we know when such tricks will
and will not work? Are there syntactic rules that
govern this? Or are they discovered by trial and
error?

For instance (strawman): Do such tricks work only
with functions that are designed normally to do
element-by-element operations on ranges, for example
as SUMPRODUCT is, but SUM is not?

 

[JMB]

mvps.org has a number of links to some good websites - some have discussion
on Sumproduct function and array formulas.

Sum can also be used in an array formula (as can many other functions).
See this links for more info.

http://xldynamic.com/source/xld.SUMPRODUCT.html
http://www.cpearson.com/excel/array.htm

Where does one find out in detail about this and
other Excel functions outside Excels own help
for example, I often see formulas such as
=SUMPRODUCT(--(A1:A10=B1:B10),(C1:C10))
[....]
where is that detailed in the help, or on the web?

I am so-o glad you asked this question. I have been
meaning to post a similar question for a long time,
but I keep forgetting.

Someone posted an answer for SUMPRODUCT per se. That
is great. Thanks.

But I would like to know the answer to the more
general question: where to find all such tricks,
which seem to be undocumented? At least, I do not
see them on the standard Help page.

For example, I undertstand the tricks used in the
SUMPRODUCT example above. But I cannot apply the
same trick (an operation on an entire range) in some
other functions, although I believe I have seen the
tricks used by others in some other functions.

Bottom line: how do we know when such tricks will
and will not work? Are there syntactic rules that
govern this? Or are they discovered by trial and
error?

For instance (strawman): Do such tricks work only
with functions that are designed normally to do
element-by-element operations on ranges, for example
as SUMPRODUCT is, but SUM is not?

 

[JMB]

Also, John Walkenbach has a book (Excel 2003 formulas -a lot of the info is
still applicable to previous versions of Excel) w/chapters devoted to array
formulas.

Where does one find out in detail about this and
other Excel functions outside Excels own help
for example, I often see formulas such as
=SUMPRODUCT(--(A1:A10=B1:B10),(C1:C10))
[....]
where is that detailed in the help, or on the web?

I am so-o glad you asked this question. I have been
meaning to post a similar question for a long time,
but I keep forgetting.

Someone posted an answer for SUMPRODUCT per se. That
is great. Thanks.

But I would like to know the answer to the more
general question: where to find all such tricks,
which seem to be undocumented? At least, I do not
see them on the standard Help page.

For example, I undertstand the tricks used in the
SUMPRODUCT example above. But I cannot apply the
same trick (an operation on an entire range) in some
other functions, although I believe I have seen the
tricks used by others in some other functions.

Bottom line: how do we know when such tricks will
and will not work? Are there syntactic rules that
govern this? Or are they discovered by trial and
error?

For instance (strawman): Do such tricks work only
with functions that are designed normally to do
element-by-element operations on ranges, for example
as SUMPRODUCT is, but SUM is not?