sumproduct problem

I have a range of cells that contain the quantity of a specific product

example:
C
1 Product A 3
2 Product B 7
3 Product C 2
....

so my range is C1:C3
now i need to calculate the total weigh of all the products
i have the list of products defined as Products, in table form
i can find the weight of a specific product using vlookup
=VLOOKUP(C1,Products,4,false)

the total weight of Product 1 would be
=C1 * VLOOKUP(C1,Products,4,false)


now i would like to use SUMPRODUCT to get the sum of the # of product X *
the weight of product X

any suggestions?

Hello,

Why not Sumif? E.g.,

=SUMIF(B1:B3,"Product A",C1:C3)

Regards,
Nate Oliver

Hi Nate

that would work for just one product - in my app there are 20 cells that
would need to be summed and 32 products to choose from - that would mean i'd
need to have 32 sumif statements



"Nate Oliver" wrote:

> Hello,
>
> Why not Sumif? E.g.,
>
> =SUMIF(B1:B3,"Product A",C1:C3)
>
> Regards,
> Nate Oliver

Gixxer

Assumptions:
Products in B2:B100
Quantity in C2:C100


Providing your list of products (Products) is sorted in ascending order,
this formula is one way of finding the total sum:

=SUMPRODUCT(LOOKUP(B2:B100,OFFSET(Products,,,,4))*C2:C100)

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Gixxer_J_97" <[email protected]> skrev i en meddelelse
news:[email protected]...
> I have a range of cells that contain the quantity of a specific product
>
> example:
> C
> 1 Product A 3
> 2 Product B 7
> 3 Product C 2
> ...
>
> so my range is C1:C3
> now i need to calculate the total weigh of all the products
> i have the list of products defined as Products, in table form
> i can find the weight of a specific product using vlookup
> =VLOOKUP(C1,Products,4,false)
>
> the total weight of Product 1 would be
> =C1 * VLOOKUP(C1,Products,4,false)
>
>
> now i would like to use SUMPRODUCT to get the sum of the # of product X *
> the weight of product X
>
> any suggestions?

Yes, but you put them in the cells H1:H20 say, and then in I1 put

=SUMIF(B$1:B$3,H1,C$1:C$3)

and copy down to I20

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Gixxer_J_97" <[email protected]> wrote in message
news:[email protected]...
> Hi Nate
>
> that would work for just one product - in my app there are 20 cells that
> would need to be summed and 32 products to choose from - that would mean
i'd
> need to have 32 sumif statements
>
>
>
> "Nate Oliver" wrote:
>
> > Hello,
> >
> > Why not Sumif? E.g.,
> >
> > =SUMIF(B1:B3,"Product A",C1:C3)
> >
> > Regards,
> > Nate Oliver

=SUMPRODUCT(C2:C10,E2:E10)

uh?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Leo Heuser" <[email protected]> wrote in message
news:[email protected]...
> Gixxer
>
> Assumptions:
> Products in B2:B100
> Quantity in C2:C100
>
>
> Providing your list of products (Products) is sorted in ascending order,
> this formula is one way of finding the total sum:
>
> =SUMPRODUCT(LOOKUP(B2:B100,OFFSET(Products4))*C2:C100)
>
> --
> Best Regards
> Leo Heuser
>
> Followup to newsgroup only please.
>
> "Gixxer_J_97" <[email protected]> sk,,,,rev i en
meddelelse
> news:[email protected]...
> > I have a range of cells that contain the quantity of a specific product
> >
> > example:
> > C
> > 1 Product A 3
> > 2 Product B 7
> > 3 Product C 2
> > ...
> >
> > so my range is C1:C3
> > now i need to calculate the total weigh of all the products
> > i have the list of products defined as Products, in table form
> > i can find the weight of a specific product using vlookup
> > =VLOOKUP(C1,Products,4,false)
> >
> > the total weight of Product 1 would be
> > =C1 * VLOOKUP(C1,Products,4,false)
> >
> >
> > now i would like to use SUMPRODUCT to get the sum of the # of product X
*
> > the weight of product X
> >
> > any suggestions?
>
>
>

Hi Bob

Sometimes I use

=SUMPRODUCT(C2:C10,E2:E10)

and sometimes

=SUMPRODUCT(C2:C10*E2:E10)

I know, that the first one is the faster one, but
my nano-watch is at the repair shop :-)

LeoH


"Bob Phillips" <[email protected]> skrev i en meddelelse
news:[email protected]...
> =SUMPRODUCT(C2:C10,E2:E10)
>
> uh?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Leo Heuser" <[email protected]> wrote in message
> news:[email protected]...
> > Gixxer
> >
> > Assumptions:
> > Products in B2:B100
> > Quantity in C2:C100
> >
> >
> > Providing your list of products (Products) is sorted in ascending
order,
> > this formula is one way of finding the total sum:
> >
> > =SUMPRODUCT(LOOKUP(B2:B100,OFFSET(Products4))*C2:C100)
> >
> > --
> > Best Regards
> > Leo Heuser
> >
> > Followup to newsgroup only please.
> >
> > "Gixxer_J_97" <[email protected]> sk,,,,rev i en
> meddelelse
> > news:[email protected]...
> > > I have a range of cells that contain the quantity of a specific
product
> > >
> > > example:
> > > C
> > > 1 Product A 3
> > > 2 Product B 7
> > > 3 Product C 2
> > > ...
> > >
> > > so my range is C1:C3
> > > now i need to calculate the total weigh of all the products
> > > i have the list of products defined as Products, in table form
> > > i can find the weight of a specific product using vlookup
> > > =VLOOKUP(C1,Products,4,false)
> > >
> > > the total weight of Product 1 would be
> > > =C1 * VLOOKUP(C1,Products,4,false)
> > >
> > >
> > > now i would like to use SUMPRODUCT to get the sum of the # of product
X
> *
> > > the weight of product X
> > >
> > > any suggestions?
> >
> >
> >
>
>

Hi Leo,

What I was referring to was (in my curtailed manner :-)) was that although
the LOOKUP embedded in the SUMPRODUCT in your original response
(=SUMPRODUCT(LOOKUP(B2:B100,OFFSET(Products,,,,4))*C2:C100)) was very
clever, was it necessary? As far as I could see, all the OP wanted (although
this was not clear from his original post) was to multiply one column by
another and then add the results, classic SP

Regards

Bob

"Leo Heuser" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob
>
> Sometimes I use
>
> =SUMPRODUCT(C2:C10,E2:E10)
>
> and sometimes
>
> =SUMPRODUCT(C2:C10*E2:E10)
>
> I know, that the first one is the faster one, but
> my nano-watch is at the repair shop :-)
>
> LeoH
>

You may be right, Bob. My attention was on
the fact, that the data column had 20 rows and
the lookup range had 32 rows (at least, that's
how I understood it), but maybe the OP will enlighten us :-)

--
Regards
LeoH


"Bob Phillips" <[email protected]> skrev i en meddelelse
news:[email protected]...
> Hi Leo,
>
> What I was referring to was (in my curtailed manner :-)) was that although
> the LOOKUP embedded in the SUMPRODUCT in your original response
> (=SUMPRODUCT(LOOKUP(B2:B100,OFFSET(Products,,,,4))*C2:C100)) was very
> clever, was it necessary? As far as I could see, all the OP wanted
(although
> this was not clear from his original post) was to multiply one column by
> another and then add the results, classic SP
>
> Regards
>
> Bob
>
> "Leo Heuser" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Bob
> >
> > Sometimes I use
> >
> > =SUMPRODUCT(C2:C10,E2:E10)
> >
> > and sometimes
> >
> > =SUMPRODUCT(C2:C10*E2:E10)
> >
> > I know, that the first one is the faster one, but
> > my nano-watch is at the repair shop :-)
> >
> > LeoH
> >
>
>

There are actually 2 x (20 x 1) row data columns - one for the product code,
one for the quantity. the lookup table for the product code (to find the
weight) is 32 rows x 18 columns. Named ranges available:

Products: the 32 x 18 lookup table
ProductList the 32 x 1 source for the product codes

this is the formula i ended up getting to work

=SUMPRODUCT((SUMIF(D17:D36,ProductList,G17:G36))*(INDEX(Products,,4)))

actually i couldn't get

=SUMPRODUCT(LOOKUP(B2:B100,OFFSET(Products,,,,4))*C2:C100)

to work. I apologize if i was unclear in my original posts - it's always
crystal when it's in your head!


"Leo Heuser" wrote:

> You may be right, Bob. My attention was on
> the fact, that the data column had 20 rows and
> the lookup range had 32 rows (at least, that's
> how I understood it), but maybe the OP will enlighten us :-)
>
> --
> Regards
> LeoH
>
>
> "Bob Phillips" <[email protected]> skrev i en meddelelse
> news:[email protected]...
> > Hi Leo,
> >
> > What I was referring to was (in my curtailed manner :-)) was that although
> > the LOOKUP embedded in the SUMPRODUCT in your original response
> > (=SUMPRODUCT(LOOKUP(B2:B100,OFFSET(Products,,,,4))*C2:C100)) was very
> > clever, was it necessary? As far as I could see, all the OP wanted
> (although
> > this was not clear from his original post) was to multiply one column by
> > another and then add the results, classic SP
> >
> > Regards
> >
> > Bob
> >
> > "Leo Heuser" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Bob
> > >
> > > Sometimes I use
> > >
> > > =SUMPRODUCT(C2:C10,E2:E10)
> > >
> > > and sometimes
> > >
> > > =SUMPRODUCT(C2:C10*E2:E10)
> > >
> > > I know, that the first one is the faster one, but
> > > my nano-watch is at the repair shop :-)
> > >
> > > LeoH
> > >
> >
> >
>
>
>
>

"Gixxer_J_97" <[email protected]> skrev i en meddelelse
news:[email protected]...
> There are actually 2 x (20 x 1) row data columns - one for the product
code,
> one for the quantity. the lookup table for the product code (to find the
> weight) is 32 rows x 18 columns. Named ranges available:
>
> Products: the 32 x 18 lookup table
> ProductList the 32 x 1 source for the product codes
>
> this is the formula i ended up getting to work
>
> =SUMPRODUCT((SUMIF(D17:D36,ProductList,G17:G36))*(INDEX(Products,,4)))
>
> actually i couldn't get
>
> =SUMPRODUCT(LOOKUP(B2:B100,OFFSET(Products,,,,4))*C2:C100)
>
> to work. I apologize if i was unclear in my original posts - it's always
> crystal when it's in your head!

Good thing, that you solved it. Next time please forward your head :-)

LeoH