Sumproduct Question #1
 

I have been doing a lot of reading on this function and can usually get it to
work to do the job - not overly complex.
One thing that is still not understood (and not the only thing) is what is
the difference between using * or , to separate the arrays in the argument?

Kevin

Sumproduct Question #1
 

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

The bad news is that Bob's site is experiencing problems.

You may want to try every so often to see if it's back up.

Kevin wrote:

I have been doing a lot of reading on this function and can usually get it to
work to do the job - not overly complex.
One thing that is still not understood (and not the only thing) is what is
the difference between using * or , to separate the arrays in the argument?

Kevin

--

Dave Peterson

Sumproduct Question #1
 

Here's a quick exercise to illustrate some of the differences:

Put these values in A1:C5
Name____Amount1__Amount2
Alpha___100______10
Bravo___200______20
Charlie_300______30
Delta___400______40

Now try these formulas:
E1: =SUMPRODUCT(--(A1:A5="Bravo"),B1:B5)......One Amount col
E2: =SUMPRODUCT(--(A1:A5="Bravo"),B1:C5)......Two Amount cols
E3: =SUMPRODUCT((A1:A5="Bravo")*B1:C5)......Incl. heading row
E4: =SUMPRODUCT((A2:A5="Bravo")*B2:C5).....Excl. heading row

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Kevin" wrote in message
...
I have been doing a lot of reading on this function and can usually get it
to
work to do the job - not overly complex.
One thing that is still not understood (and not the only thing) is what is
the difference between using * or , to separate the arrays in the
argument?

Kevin

Sumproduct Question #1
 

The determining factor as to what form to use should be by how the
calculating data range(s) are to be populated.

The main difference between the unary form and the asterisk form is that
when using the asterisk, *all* the data in the calculating range(s) *must*
be numeric.
That numeric data can even be numeric text - just as long as it looks like a
number, the asterisk form will calculate it.
If it's not in any numeric form, it will return an error.

The unary form will simply by-pass any text or non-numeric data, and
complete the calculation using only *real XL recognized* numbers.

Therefore, if you're populating your calculating ranges with formulas that
may return nulls ( "" ), or text messages (such as "No Data Present"), then
the unary form is the *only* way to go.

However, if data is to be either keyed in, or imported, the asterisk form
*should* be the form of choice, since it will calculate the various forms of
numbers that are usually imported from other apps or the web.
Imported numeric text will by-passed with the unary form, without any
indication as to what data was or was not used in the calculation, which may
produce inaccurate returns.

Another difference between the two forms, is that the asterisk form *must*
be used when *uneven* range sizes are incorporated in calculating 2
dimensional (row v. column) formulas.

With all things being equal, the unary form is supposedly the faster to
calculate, making it the choice for very large ranges.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Kevin" wrote in message
...
I have been doing a lot of reading on this function and can usually get it
to
work to do the job - not overly complex.
One thing that is still not understood (and not the only thing) is what is
the difference between using * or , to separate the arrays in the
argument?

Kevin

Sumproduct Question #1 -- Thanks
 

Thank you for the responses, they were very helpful.
I am certain that Question #2 will be posted as my usage progresses.

Kevin

"Kevin" wrote:

I have been doing a lot of reading on this function and can usually get it to
work to do the job - not overly complex.
One thing that is still not understood (and not the only thing) is what is
the difference between using * or , to separate the arrays in the argument?

Kevin

Sumproduct Question #1 -- Thanks
 

We appreciate your feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Kevin" wrote in message
...
Thank you for the responses, they were very helpful.
I am certain that Question #2 will be posted as my usage progresses.

Kevin

"Kevin" wrote:

I have been doing a lot of reading on this function and can usually get it
to
work to do the job - not overly complex.
One thing that is still not understood (and not the only thing) is what is
the difference between using * or , to separate the arrays in the
argument?

Kevin