Victor Chapman wrote:
JulieD wrote:

Hi Victor

Probably the best reference for information on the sumproduct function
that i know about is at
http://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.

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 http://www.xldynamic.com/source/xld.SUMPRODUCT.html and
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.