VLookup & Column Sum

[ddate]

I need help with a formula that will be able to lookup a part name and when
it finds it a worksheet it can sum the weekly values to return the monthly
total.

data table:
Part Wk 1 Wk 2 Wk 3 Wk 4
pen 10 20 30 40
clip 20 30 40 50

Formula needs to vlookup Pen and return the sum of wks 1-4 = 100. Was
researching SUMPRODUCT but not sure if that's the solution and unclear on how
to apply it. I am hoping for a formula that is an array instead of needing
to add multiple vlookups together. Also if anyone has a good link to explain
SUMPRODUCT I would appreciate it. Thanks in advance.

[Richard Buttrey]

On Wed, 8 Mar 2006 15:19:27 -0800, ddate
<ddate@discussions.microsoft.com> wrote:

>I need help with a formula that will be able to lookup a part name and when
>it finds it a worksheet it can sum the weekly values to return the monthly
>total.
>
>data table:
>Part Wk 1 Wk 2 Wk 3 Wk 4
>pen 10 20 30 40
>clip 20 30 40 50
>
>Formula needs to vlookup Pen and return the sum of wks 1-4 = 100. Was
>researching SUMPRODUCT but not sure if that's the solution and unclear on how
>to apply it. I am hoping for a formula that is an array instead of needing
>to add multiple vlookups together. Also if anyone has a good link to explain
>SUMPRODUCT I would appreciate it. Thanks in advance.

With the tabel above in A1:E3

=SUMPRODUCT((A2:A3="pen")*(B2:E3))

An excellent link to SUMPRODUCT is:

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

HTH
Richard Buttrey
__