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

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" wrote in message
...
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" skrev i en meddelelse
...
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?

=SUMPRODUCT(C2:C10,E2:E10)

uh?

--

HTH

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


"Leo Heuser" wrote in message
...
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:C 100)

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Gixxer_J_97" sk,,,,rev i en
meddelelse
...
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" skrev i en meddelelse
...
=SUMPRODUCT(C2:C10,E2:E10)

uh?

--

HTH

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


"Leo Heuser" wrote in message
...
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:C 100)

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Gixxer_J_97" sk,,,,rev i en
meddelelse
...
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" wrote in message
...
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" skrev i en meddelelse
...
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" wrote in message
...
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