Hello everyone

I'm trying to populate data from one database to another
I have the Product Type and Code of different products, and receive the
Sales via SUMPROD

=+SUMPRODUCT((Sheet5!$E$2:$E$8296=Sheet3!D2)*(Shee t5!$F$2:$F$8296=Sheet3!E2)*(Sheet5!$H$2:$H$8296))

Now, the problem is that one product can have 2 Codes
I have on Sheet4 on colA the Product Type, on colB the Code1 and colC Code2
since i want the sales per product, i need to sum the sales of both codes
(if applicable)

i cant figure out how to include the Code2
a sumprod within the sumprod??
another sumprod?? if so, how??
vlookup?? (Sheet5!$F$2:$F$8296=VLOOKUP(Sheet3!E2,Sheet4!...)
a code number can repeat for diff PT

Modify this idea to suit
=SUMPRODUCT((TRIM(ChecksC)={"electricity","water"} )*(ChecksD))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Alonso" wrote in message
...
Hello everyone

I'm trying to populate data from one database to another
I have the Product Type and Code of different products, and receive the
Sales via SUMPROD

=+SUMPRODUCT((Sheet5!$E$2:$E$8296=Sheet3!D2)*(Shee t5!$F$2:$F$8296=Sheet3!E2)*(Sheet5!$H$2:$H$8296))

Now, the problem is that one product can have 2 Codes
I have on Sheet4 on colA the Product Type, on colB the Code1 and colC
Code2
since i want the sales per product, i need to sum the sales of both codes
(if applicable)

i cant figure out how to include the Code2
a sumprod within the sumprod??
another sumprod?? if so, how??
vlookup?? (Sheet5!$F$2:$F$8296=VLOOKUP(Sheet3!E2,Sheet4!...)
a code number can repeat for diff PT

Hi,

I'm not clear on this - your original formula refers to Sheet5 but your
later discussion refers to Sheet4? Also you say "Now, the problem is that
one product can have 2 Codes
I have on Sheet4 on colA the Product Type, on colB the Code1 and colC Code2
since i want the sales per product, i need to sum the sales of both codes.

So if you want to sum one product but it can have two codes and you want to
sum them both, what difference does the code matter, why not just sum the
price for the product?


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Alonso" wrote:

Hello everyone

I'm trying to populate data from one database to another
I have the Product Type and Code of different products, and receive the
Sales via SUMPROD

=+SUMPRODUCT((Sheet5!$E$2:$E$8296=Sheet3!D2)*(Shee t5!$F$2:$F$8296=Sheet3!E2)*(Sheet5!$H$2:$H$8296))

Now, the problem is that one product can have 2 Codes
I have on Sheet4 on colA the Product Type, on colB the Code1 and colC Code2
since i want the sales per product, i need to sum the sales of both codes
(if applicable)

i cant figure out how to include the Code2
a sumprod within the sumprod??
another sumprod?? if so, how??
vlookup?? (Sheet5!$F$2:$F$8296=VLOOKUP(Sheet3!E2,Sheet4!...)
a code number can repeat for diff PT

Hi Shane

To clarify
on sheet3 it's the database i need to populate with the sales, that are on
sheet5
on both sheets i only have the codes, not the product name
so i cant do a sumprod based on the name
it has to be done by codes

i have a catalog on sheet4 with the codes
so, my formula returns the sales registered on sheet5 for codes on sheet3
but now i need to check if the same code on sheet3 has another "alias" code
in sheet4, search for it on sheet5 and add to the first code

hope its clearer now


"Shane Devenshire" wrote:

Hi,

I'm not clear on this - your original formula refers to Sheet5 but your
later discussion refers to Sheet4? Also you say "Now, the problem is that
one product can have 2 Codes
I have on Sheet4 on colA the Product Type, on colB the Code1 and colC Code2
since i want the sales per product, i need to sum the sales of both codes.

So if you want to sum one product but it can have two codes and you want to
sum them both, what difference does the code matter, why not just sum the
price for the product?


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Maybe something like this:

=SUMPRODUCT((--OR(Sheet5!$E$2:$E$8296=Sheet3!D2,Sheet5!$E$2:$E$82 96=VLOOKUP(Sheet3!D2,Sheet4!range,2))*(Sheet5!$H$2 :$H$8296))

Alonso wrote:
Hi Shane

To clarify
on sheet3 it's the database i need to populate with the sales, that are on
sheet5
on both sheets i only have the codes, not the product name
so i cant do a sumprod based on the name
it has to be done by codes

i have a catalog on sheet4 with the codes
so, my formula returns the sales registered on sheet5 for codes on sheet3
but now i need to check if the same code on sheet3 has another "alias" code
in sheet4, search for it on sheet5 and add to the first code

hope its clearer now


"Shane Devenshire" wrote:

Hi,

I'm not clear on this - your original formula refers to Sheet5 but your
later discussion refers to Sheet4? Also you say "Now, the problem is that
one product can have 2 Codes
I have on Sheet4 on colA the Product Type, on colB the Code1 and colC Code2
since i want the sales per product, i need to sum the sales of both codes.

So if you want to sum one product but it can have two codes and you want to
sum them both, what difference does the code matter, why not just sum the
price for the product?


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Thank you Glenn

I tried your suggestion
trying to include at the same time the customer code
it ended like this:

=+SUMPRODUCT((A33=Sheet5!$A$2:$A$8296)*(Sheet5!$C$ 2:$C$8296=Sheet3!B33)*(--OR(Sheet5!$E$2:$E$8296=Sheet3!D33,Sheet5!$E$2:$E$8 296=VLOOKUP(Sheet3!D33,Sheet4!$A$2:$D$7058,4)))*(--OR(Sheet5!$E$2:$E$8296=Sheet3!E33,Sheet5!$E$2:$E$8 296=VLOOKUP(Sheet3!E33,Sheet4!$B$2:$E$7058,4)))*(S heet5!$H$2:$H$8296))

i also tried splited
=+SUMPRODUCT((A2=Sheet5!$A$2:$A$8296)*(Sheet5!$C$2 :$C$8296=Sheet3!B2)*(Sheet5!$E$2:$E$8296=Sheet3!D2 )*(Sheet5!$E$2:$E$8296=Sheet3!E2)*(Sheet5!$H$2:$H$ 8296))+SUMPRODUCT((A2=Sheet5!$A$2:$A$8296)*(Sheet5 !$C$2:$C$8296=Sheet3!B2)*(Sheet5!$E$2:$E$8296=VLOO KUP(Sheet3!D2,Sheet4!$A$2:$D$7058,4))*(Sheet5!$E$2 :$E$8296=VLOOKUP(Sheet3!E2,Sheet4!$B$2:$E$7058,4)) *(Sheet5!$H$2:$H$8296))

both formulas return zeroes
in products that reported sales




"Glenn" wrote:

Maybe something like this:

=SUMPRODUCT((--OR(Sheet5!$E$2:$E$8296=Sheet3!D2,Sheet5!$E$2:$E$82 96=VLOOKUP(Sheet3!D2,Sheet4!range,2))*(Sheet5!$H$2 :$H$8296))

Alonso wrote:
Hi Shane

To clarify
on sheet3 it's the database i need to populate with the sales, that are on
sheet5
on both sheets i only have the codes, not the product name
so i cant do a sumprod based on the name
it has to be done by codes

i have a catalog on sheet4 with the codes
so, my formula returns the sales registered on sheet5 for codes on sheet3
but now i need to check if the same code on sheet3 has another "alias" code
in sheet4, search for it on sheet5 and add to the first code

hope its clearer now


"Shane Devenshire" wrote:

Hi,

I'm not clear on this - your original formula refers to Sheet5 but your
later discussion refers to Sheet4? Also you say "Now, the problem is that
one product can have 2 Codes
I have on Sheet4 on colA the Product Type, on colB the Code1 and colC Code2
since i want the sales per product, i need to sum the sales of both codes.

So if you want to sum one product but it can have two codes and you want to
sum them both, what difference does the code matter, why not just sum the
price for the product?


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

I'm sorry
double checked the formulas
the $E is repeated
seems it´s working now

thanks for your help

"Alonso" wrote:

Thank you Glenn

I tried your suggestion
trying to include at the same time the customer code
it ended like this:

=+SUMPRODUCT((A33=Sheet5!$A$2:$A$8296)*(Sheet5!$C$ 2:$C$8296=Sheet3!B33)*(--OR(Sheet5!$E$2:$E$8296=Sheet3!D33,Sheet5!$E$2:$E$8 296=VLOOKUP(Sheet3!D33,Sheet4!$A$2:$D$7058,4)))*(--OR(Sheet5!$E$2:$E$8296=Sheet3!E33,Sheet5!$E$2:$E$8 296=VLOOKUP(Sheet3!E33,Sheet4!$B$2:$E$7058,4)))*(S heet5!$H$2:$H$8296))

i also tried splited
=+SUMPRODUCT((A2=Sheet5!$A$2:$A$8296)*(Sheet5!$C$2 :$C$8296=Sheet3!B2)*(Sheet5!$E$2:$E$8296=Sheet3!D2 )*(Sheet5!$E$2:$E$8296=Sheet3!E2)*(Sheet5!$H$2:$H$ 8296))+SUMPRODUCT((A2=Sheet5!$A$2:$A$8296)*(Sheet5 !$C$2:$C$8296=Sheet3!B2)*(Sheet5!$E$2:$E$8296=VLOO KUP(Sheet3!D2,Sheet4!$A$2:$D$7058,4))*(Sheet5!$E$2 :$E$8296=VLOOKUP(Sheet3!E2,Sheet4!$B$2:$E$7058,4)) *(Sheet5!$H$2:$H$8296))

both formulas return zeroes
in products that reported sales




"Glenn" wrote:

Maybe something like this:

=SUMPRODUCT((--OR(Sheet5!$E$2:$E$8296=Sheet3!D2,Sheet5!$E$2:$E$82 96=VLOOKUP(Sheet3!D2,Sheet4!range,2))*(Sheet5!$H$2 :$H$8296))

Alonso wrote:
Hi Shane

To clarify
on sheet3 it's the database i need to populate with the sales, that are on
sheet5
on both sheets i only have the codes, not the product name
so i cant do a sumprod based on the name
it has to be done by codes

i have a catalog on sheet4 with the codes
so, my formula returns the sales registered on sheet5 for codes on sheet3
but now i need to check if the same code on sheet3 has another "alias" code
in sheet4, search for it on sheet5 and add to the first code

hope its clearer now


"Shane Devenshire" wrote:

Hi,

I'm not clear on this - your original formula refers to Sheet5 but your
later discussion refers to Sheet4? Also you say "Now, the problem is that
one product can have 2 Codes
I have on Sheet4 on colA the Product Type, on colB the Code1 and colC Code2
since i want the sales per product, i need to sum the sales of both codes.

So if you want to sum one product but it can have two codes and you want to
sum them both, what difference does the code matter, why not just sum the
price for the product?


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

OR won't work in SUMPRODUCT like that. OR returns a single result where you
need an array of results. If any element in the OR test is true you'd get
this:

1*Sheet5!$H$2:$H$8296

which would sum the entire range even if the conditions weren't met on
individual rows:

x.....y.....1
o.....c.....1
z.....p.....1

=SUMPRODUCT((--OR(A1:A3="x",B1:B3="y"))*(C1:C3))

Result = 3

Try it like this:

=SUMPRODUCT(--((A1:A3="x")+(B1:B3="y")0),C1:C3)

Or:

=SUMPRODUCT(SIGN((A1:A3="x")+(B1:B3="y")),C1:C3)

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
Maybe something like this:

=SUMPRODUCT((--OR(Sheet5!$E$2:$E$8296=Sheet3!D2,Sheet5!$E$2:$E$82 96=VLOOKUP(Sheet3!D2,Sheet4!range,2))*(Sheet5!$H$2 :$H$8296))

Alonso wrote:
Hi Shane

To clarify
on sheet3 it's the database i need to populate with the sales, that are
on sheet5
on both sheets i only have the codes, not the product name
so i cant do a sumprod based on the name
it has to be done by codes

i have a catalog on sheet4 with the codes
so, my formula returns the sales registered on sheet5 for codes on sheet3
but now i need to check if the same code on sheet3 has another "alias"
code in sheet4, search for it on sheet5 and add to the first code

hope its clearer now


"Shane Devenshire" wrote:

Hi,

I'm not clear on this - your original formula refers to Sheet5 but your
later discussion refers to Sheet4? Also you say "Now, the problem is
that one product can have 2 Codes
I have on Sheet4 on colA the Product Type, on colB the Code1 and colC
Code2
since i want the sales per product, i need to sum the sales of both
codes.
So if you want to sum one product but it can have two codes and you want
to sum them both, what difference does the code matter, why not just sum
the price for the product?


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

My most recent try
still not good
cause the vlookup can find another product type or code

=+SUMPRODUCT((A3=Sales!$A$2:$A$8296)*(Sales!$C$2:$ C$8296=B3)*(Sales!$E$2:$E$8296=D3)*(Sales!$F$2:$F$ 8296=E3)*(Sales!$H$2:$H$8296))+SUMPRODUCT((A3=Sale s!$A$2:$A$8296)*(Sales!$C$2:$C$8296=B3)*(Sales!$E$ 2:$E$8296=VLOOKUP(D3,ALIAS!$A$2:$D$7058,4,0))*(Sal es!$F$2:$F$8296=VLOOKUP(E3,ALIAS!$B$2:$E$7058,4,0) )*(Sales!$H$2:$H$8296))




"T. Valko" wrote:

OR won't work in SUMPRODUCT like that. OR returns a single result where you
need an array of results. If any element in the OR test is true you'd get
this:

1*Sheet5!$H$2:$H$8296

which would sum the entire range even if the conditions weren't met on
individual rows:

x.....y.....1
o.....c.....1
z.....p.....1

=SUMPRODUCT((--OR(A1:A3="x",B1:B3="y"))*(C1:C3))

Result = 3

Try it like this:

=SUMPRODUCT(--((A1:A3="x")+(B1:B3="y")0),C1:C3)

Or:

=SUMPRODUCT(SIGN((A1:A3="x")+(B1:B3="y")),C1:C3)

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
Maybe something like this:

=SUMPRODUCT((--OR(Sheet5!$E$2:$E$8296=Sheet3!D2,Sheet5!$E$2:$E$82 96=VLOOKUP(Sheet3!D2,Sheet4!range,2))*(Sheet5!$H$2 :$H$8296))

Alonso wrote:
Hi Shane

To clarify
on sheet3 it's the database i need to populate with the sales, that are
on sheet5
on both sheets i only have the codes, not the product name
so i cant do a sumprod based on the name
it has to be done by codes

i have a catalog on sheet4 with the codes
so, my formula returns the sales registered on sheet5 for codes on sheet3
but now i need to check if the same code on sheet3 has another "alias"
code in sheet4, search for it on sheet5 and add to the first code

hope its clearer now


"Shane Devenshire" wrote:

Hi,

I'm not clear on this - your original formula refers to Sheet5 but your
later discussion refers to Sheet4? Also you say "Now, the problem is
that one product can have 2 Codes
I have on Sheet4 on colA the Product Type, on colB the Code1 and colC
Code2
since i want the sales per product, i need to sum the sales of both
codes.
So if you want to sum one product but it can have two codes and you want
to sum them both, what difference does the code matter, why not just sum
the price for the product?


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

I'm having trouble trying to envision your setup.

If you want to setup a *small* sample file (20 or 30 rows of data) and
upload it to a file host I'll take a look it. You can use a free file host.
One I use frequently is:

CJoint

http://tinyurl.com/24xfnt

It's a French site that's been translated to English.

You upload the file and it'll give you a link to that file. Then you can
post the link and we can download the file and take a look and see what's
up.

--
Biff
Microsoft Excel MVP


"Alonso" wrote in message
...
My most recent try
still not good
cause the vlookup can find another product type or code

=+SUMPRODUCT((A3=Sales!$A$2:$A$8296)*(Sales!$C$2:$ C$8296=B3)*(Sales!$E$2:$E$8296=D3)*(Sales!$F$2:$F$ 8296=E3)*(Sales!$H$2:$H$8296))+SUMPRODUCT((A3=Sale s!$A$2:$A$8296)*(Sales!$C$2:$C$8296=B3)*(Sales!$E$ 2:$E$8296=VLOOKUP(D3,ALIAS!$A$2:$D$7058,4,0))*(Sal es!$F$2:$F$8296=VLOOKUP(E3,ALIAS!$B$2:$E$7058,4,0) )*(Sales!$H$2:$H$8296))




"T. Valko" wrote:

OR won't work in SUMPRODUCT like that. OR returns a single result where
you
need an array of results. If any element in the OR test is true you'd get
this:

1*Sheet5!$H$2:$H$8296

which would sum the entire range even if the conditions weren't met on
individual rows:

x.....y.....1
o.....c.....1
z.....p.....1

=SUMPRODUCT((--OR(A1:A3="x",B1:B3="y"))*(C1:C3))

Result = 3

Try it like this:

=SUMPRODUCT(--((A1:A3="x")+(B1:B3="y")0),C1:C3)

Or:

=SUMPRODUCT(SIGN((A1:A3="x")+(B1:B3="y")),C1:C3)

--
Biff
Microsoft Excel MVP


"Glenn" wrote in message
...
Maybe something like this:

=SUMPRODUCT((--OR(Sheet5!$E$2:$E$8296=Sheet3!D2,Sheet5!$E$2:$E$82 96=VLOOKUP(Sheet3!D2,Sheet4!range,2))*(Sheet5!$H$2 :$H$8296))

Alonso wrote:
Hi Shane

To clarify
on sheet3 it's the database i need to populate with the sales, that
are
on sheet5
on both sheets i only have the codes, not the product name
so i cant do a sumprod based on the name
it has to be done by codes

i have a catalog on sheet4 with the codes
so, my formula returns the sales registered on sheet5 for codes on
sheet3
but now i need to check if the same code on sheet3 has another "alias"
code in sheet4, search for it on sheet5 and add to the first code

hope its clearer now


"Shane Devenshire" wrote:

Hi,

I'm not clear on this - your original formula refers to Sheet5 but
your
later discussion refers to Sheet4? Also you say "Now, the problem is
that one product can have 2 Codes
I have on Sheet4 on colA the Product Type, on colB the Code1 and colC
Code2
since i want the sales per product, i need to sum the sales of both
codes.
So if you want to sum one product but it can have two codes and you
want
to sum them both, what difference does the code matter, why not just
sum
the price for the product?


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

Thanks Biff
Seems I figured out
using a concatenation on sheet4 and then a vlookup(A1&B1,....)

an idea just came
maybe an index/match??
too late
hahaha



"T. Valko" wrote:

I'm having trouble trying to envision your setup.

If you want to setup a *small* sample file (20 or 30 rows of data) and
upload it to a file host I'll take a look it. You can use a free file host.
One I use frequently is:

CJoint

http://tinyurl.com/24xfnt

It's a French site that's been translated to English.

You upload the file and it'll give you a link to that file. Then you can
post the link and we can download the file and take a look and see what's
up.

--
Biff
Microsoft Excel MVP

OK, thanks for feeding back.

--
Biff
Microsoft Excel MVP


"Alonso" wrote in message
...
Thanks Biff
Seems I figured out
using a concatenation on sheet4 and then a vlookup(A1&B1,....)

an idea just came
maybe an index/match??
too late
hahaha



"T. Valko" wrote:

I'm having trouble trying to envision your setup.

If you want to setup a *small* sample file (20 or 30 rows of data) and
upload it to a file host I'll take a look it. You can use a free file
host.
One I use frequently is:

CJoint

http://tinyurl.com/24xfnt

It's a French site that's been translated to English.

You upload the file and it'll give you a link to that file. Then you can
post the link and we can download the file and take a look and see what's
up.

--
Biff
Microsoft Excel MVP