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