Double SUMPROD

[Alonso]

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)*(Sheet5!$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

 

[Don Guillett]

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

 

[Shane Devenshire]

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?

 

[Alonso]

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

 

[Glenn]

Maybe something like this:

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

 

[Alonso]

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$8296=VLOOKUP(Sheet3!D33,Sheet4!$A$2:$D$7058,4)))*(--OR(Sheet5!$E$2:$E$8296=Sheet3!E33,Sheet5!$E$2:$E$8296=VLOOKUP(Sheet3!E33,Sheet4!$B$2:$E$7058,4)))*(Sheet5!$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=VLOOKUP(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

 

[Alonso]

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

thanks for your help

 

[T. Valko]

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)

 

[Alonso]

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=Sales!$A$2:$A$8296)*(Sales!$C$2:$C$8296=B3)*(Sales!$E$2:$E$8296=VLOOKUP(D3,ALIAS!$A$2:$D$7058,4,0))*(Sales!$F$2:$F$8296=VLOOKUP(E3,ALIAS!$B$2:$E$7058,4,0))*(Sales!$H$2:$H$8296))

 

[T. Valko]

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.

 

[Alonso]

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]

OK, thanks for feeding back.

--
Biff
Microsoft Excel MVP

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