software

This maybe a strange question, but I have a SUMPRODUCT function that
works in Excel, however, I don't understand why it does.

Here is what I have:

An array of task times (Duration) B3:B41

A matrix of names of people assigned to the various tasks throughout the
week (WEEK)D3:H41 (The names appear one or more times)

The array of individual names appears in A43:A61

Now I want to know the total time each individual spends carrying out
one or more of the tasks. This is done with:

SUMPRODUCT((WEEK=$A43) * Duration)

This function appears beside each name with the row number incremented
accordingly. IT WORKS!

Here is my problem, I can't find anywhere in the documentation for this
function (or another example) where a row number in a matrix will look
up the corresponding row number in an array. So I don't understand why
it provides the desired result.

I would appreciate more examples of this use of SUMPRODUCT and technical
explanation of why it works.

TIA
--

_______________________________
Regards,
Vic Chapman

Hi!

(WEEK=$A43) will return an array of boolean TRUE or FALSE. Something like
this:

D3 = A43 = TRUE
D4 = A43 = FALSE
D5 = A43 = TRUE
D6 = A43 = FALSE

Then those boolean values are multiplied by the corresponding duration
values from the other array, B3:B41. That would look like this:

TRUE * B3 = B3
FALSE * B4 = 0
TRUE * B5 = B5
FALSE * B6 = 0

Then the values are summed together and you get your result.

There's a very detailed explanation of Sumproduct he

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

Biff

quot;Victor Chapmanquot; gt; wrote in message
. ..
gt; This maybe a strange question, but I have a SUMPRODUCT function that works
gt; in Excel, however, I don't understand why it does.
gt;
gt; Here is what I have:
gt;
gt; An array of task times (Duration) B3:B41
gt;
gt; A matrix of names of people assigned to the various tasks throughout the
gt; week (WEEK)D3:H41 (The names appear one or more times)
gt;
gt; The array of individual names appears in A43:A61
gt;
gt; Now I want to know the total time each individual spends carrying out one
gt; or more of the tasks. This is done with:
gt;
gt; SUMPRODUCT((WEEK=$A43) * Duration)
gt;
gt; This function appears beside each name with the row number incremented
gt; accordingly. IT WORKS!
gt;
gt; Here is my problem, I can't find anywhere in the documentation for this
gt; function (or another example) where a row number in a matrix will look up
gt; the corresponding row number in an array. So I don't understand why it
gt; provides the desired result.
gt;
gt; I would appreciate more examples of this use of SUMPRODUCT and technical
gt; explanation of why it works.
gt;
gt; TIA
gt;
gt;
gt;
gt; --
gt;
gt; _______________________________
gt; Regards,
gt; Vic Chapman
Hi Victor

Probably the best reference for information on the sumproduct function that
i know about is at

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

But basically the sumproduct function works by evaluation true statements to
1 and false statements to 0 ...

For a quick overview of your sumproduct funtion look at it this way:
=SUMPRODUCT((WEEK=$A43) * Duration)
=SUMPRODUCT((D3:H41 =$A43)*B3:B41)
(for this example i'm make the ranges smaller .... e.g.)
=SUMPRODUCT((D1:F3 =$G1)*B1:B3)
(and use the following data)
.......A........B.........C.........D........E.... .....F.............G
1.............10...................Bill......Fred. ...Steve.......Anne
2.............15...................Anne...Bill.... ...Fred........Bill
3.............20...................Fred....Anne... .Steve......Steve

In G2 the SUMPRODUCT formula would work like this
=SUMPRODUCT((D1:F3 =$G1)*B1:B3)
=SUMPRODUCT((Bill, Fred, Steve, Anne, Bill, Fred, Fred, Anne,
Steve=Anne)*(10,10,10,15,15,15,20,20,20))
=SUMPRODUCT((False, False, False, True, False, False, False, True,
False)*(10,10,10,15,15,15,20,20,20))
=SUMPROUDCT((0,0,0,1,0,0,0,1,0)*(10,10,10,15,15,15 ,20,20,20))
=SUMPRODUCT(0*10 0*10 0*10 1*15 0*15 0*15 0*20 1*2 0 0*20)
=SUMPRODUCT(0 0 0 15 0 0 0 20 0)
=35

Hope this helps.

--
Cheers
JulieD
Excel MVP

julied_ng at hctsReMoVeThIs dot net dot auquot;Victor Chapmanquot; wrote:

gt; This maybe a strange question, but I have a SUMPRODUCT function that
gt; works in Excel, however, I don't understand why it does.
gt;
gt; Here is what I have:
gt;
gt; An array of task times (Duration) B3:B41
gt;
gt; A matrix of names of people assigned to the various tasks throughout the
gt; week (WEEK)D3:H41 (The names appear one or more times)
gt;
gt; The array of individual names appears in A43:A61
gt;
gt; Now I want to know the total time each individual spends carrying out
gt; one or more of the tasks. This is done with:
gt;
gt; SUMPRODUCT((WEEK=$A43) * Duration)
gt;
gt; This function appears beside each name with the row number incremented
gt; accordingly. IT WORKS!
gt;
gt; Here is my problem, I can't find anywhere in the documentation for this
gt; function (or another example) where a row number in a matrix will look
gt; up the corresponding row number in an array. So I don't understand why
gt; it provides the desired result.
gt;
gt; I would appreciate more examples of this use of SUMPRODUCT and technical
gt; explanation of why it works.
gt;
gt; TIA
gt;
gt;
gt;
gt; --
gt;
gt; _______________________________
gt; Regards,
gt; Vic Chapman
gt;

JulieD wrote:
gt; Hi Victor
gt;
gt; Probably the best reference for information on the sumproduct function that
gt; i know about is at
gt;
gt; www.xldynamic.com/source/xld.SUMPRODUCT.html
gt;
gt; But basically the sumproduct function works by evaluation true statements to
gt; 1 and false statements to 0 ...
gt;
gt; For a quick overview of your sumproduct funtion look at it this way:
gt; =SUMPRODUCT((WEEK=$A43) * Duration)
gt; =SUMPRODUCT((D3:H41 =$A43)*B3:B41)
gt; (for this example i'm make the ranges smaller .... e.g.)
gt; =SUMPRODUCT((D1:F3 =$G1)*B1:B3)
gt; (and use the following data)
gt; ......A........B.........C.........D........E..... ....F.............G
gt; 1.............10...................Bill......Fred. ...Steve.......Anne
gt; 2.............15...................Anne...Bill.... ...Fred........Bill
gt; 3.............20...................Fred....Anne... .Steve......Steve
gt;
gt; In G2 the SUMPRODUCT formula would work like this
gt; =SUMPRODUCT((D1:F3 =$G1)*B1:B3)
gt; =SUMPRODUCT((Bill, Fred, Steve, Anne, Bill, Fred, Fred, Anne,
gt; Steve=Anne)*(10,10,10,15,15,15,20,20,20))
gt; =SUMPRODUCT((False, False, False, True, False, False, False, True,
gt; False)*(10,10,10,15,15,15,20,20,20))
gt; =SUMPROUDCT((0,0,0,1,0,0,0,1,0)*(10,10,10,15,15,15 ,20,20,20))
gt; =SUMPRODUCT(0*10 0*10 0*10 1*15 0*15 0*15 0*20 1*2 0 0*20)
gt; =SUMPRODUCT(0 0 0 15 0 0 0 20 0)
gt; =35
gt;
gt; Hope this helps.
gt;
Thank you for your quick response. I agree, and understand that this is
what the function is doing. What I don't understand is why this works
when it it would appear to be contrary to the information provided in
the Excel Help system. The following is copied from the Help system:

The array arguments must have the same dimensions. If they do not,
SUMPRODUCT returns the #VALUE! error value.

In the example I have provided, Duration is a single column array. WEEK
on the other hand is a multiple column matrix. They have different
dimensions!

To work from your example:

=SUMPRODUCT((D1:F3 =$G1)*B1:B3)

is NOT the same as

=SUMPRODUCT((Bill, Fred, Steve, Anne, Bill, Fred, Fred, Anne,
Steve=Anne)*(10,10,10,15,15,15,20,20,20))

It makes the assumption that for every instance of a row value in WEEK,
the corresponding row value in Duration will be generated. I can't find
anywhere in the documentation that I can make that assumption. I have
also checked www.xldynamic.com/source/xld.SUMPRODUCT.html and
cannot find an example similar to the one I have provide.
--

_______________________________
Regards,
Vic Chapman



Victor Chapman wrote:
gt; JulieD wrote:
gt;
gt;gt; Hi Victor
gt;gt;
gt;gt; Probably the best reference for information on the sumproduct function
gt;gt; that i know about is at
gt;gt; www.xldynamic.com/source/xld.SUMPRODUCT.html
gt;gt;
gt;gt; But basically the sumproduct function works by evaluation true
gt;gt; statements to 1 and false statements to 0 ...
gt;gt;
gt;gt; For a quick overview of your sumproduct funtion look at it this way:
gt;gt; =SUMPRODUCT((WEEK=$A43) * Duration)
gt;gt; =SUMPRODUCT((D3:H41 =$A43)*B3:B41)
gt;gt; (for this example i'm make the ranges smaller .... e.g.)
gt;gt; =SUMPRODUCT((D1:F3 =$G1)*B1:B3)
gt;gt; (and use the following data)
gt;gt; ......A........B.........C.........D........E..... ....F.............G
gt;gt; 1.............10...................Bill......Fred. ...Steve.......Anne
gt;gt; 2.............15...................Anne...Bill.... ...Fred........Bill
gt;gt; 3.............20...................Fred....Anne... .Steve......Steve
gt;gt;
gt;gt; In G2 the SUMPRODUCT formula would work like this
gt;gt; =SUMPRODUCT((D1:F3 =$G1)*B1:B3)
gt;gt; =SUMPRODUCT((Bill, Fred, Steve, Anne, Bill, Fred, Fred, Anne,
gt;gt; Steve=Anne)*(10,10,10,15,15,15,20,20,20))
gt;gt; =SUMPRODUCT((False, False, False, True, False, False, False, True,
gt;gt; False)*(10,10,10,15,15,15,20,20,20))
gt;gt; =SUMPROUDCT((0,0,0,1,0,0,0,1,0)*(10,10,10,15,15,15 ,20,20,20))
gt;gt; =SUMPRODUCT(0*10 0*10 0*10 1*15 0*15 0*15 0*20 1*2 0 0*20)
gt;gt; =SUMPRODUCT(0 0 0 15 0 0 0 20 0)
gt;gt; =35
gt;gt;
gt;gt; Hope this helps.
gt;gt;
gt; Thank you for your quick response. I agree, and understand that this is
gt; what the function is doing. What I don't understand is why this works
gt; when it it would appear to be contrary to the information provided in
gt; the Excel Help system. The following is copied from the Help system:
gt;
gt; The array arguments must have the same dimensions. If they do not,
gt; SUMPRODUCT returns the #VALUE! error value.
gt;
gt; In the example I have provided, Duration is a single column array. WEEK
gt; on the other hand is a multiple column matrix. They have different
gt; dimensions!
gt;
gt; To work from your example:
gt;
gt; =SUMPRODUCT((D1:F3 =$G1)*B1:B3)
gt;
gt; is NOT the same as
gt;
gt; =SUMPRODUCT((Bill, Fred, Steve, Anne, Bill, Fred, Fred, Anne,
gt; Steve=Anne)*(10,10,10,15,15,15,20,20,20))
gt;
gt; It makes the assumption that for every instance of a row value in WEEK,
gt; the corresponding row value in Duration will be generated. I can't find
gt; anywhere in the documentation that I can make that assumption. I have
gt; also checked www.xldynamic.com/source/xld.SUMPRODUCT.html and
gt; cannot find an example similar to the one I have provide.

You are multiplying, as it were, a vector with a matrix which are
equally sized in one relevant dimension.