I'm stuck putting together a formula to lookup multiple values in another table based on more than one cell of criteria. I don't have a problem doing the sumif or sumproduct functions, but only that the criteria needs to equal more than one cell.

For instance, this is the formula I'm using. I started with SUMIF, but then thought SUMPRODUCT might work better:
=SUMPRODUCT(('sales worksheet'!$B$6:$B$431=CC11:CJ11)*'sales worksheet'!$W$6:$W$431))
where it is looking up account #s in the sales worksheet that match the criteria in columns CC11:CJ11.
Works great, but it only retrieves the first account # in columns CC11:CJ11. That is my dilemma. I've tried using HLOOKUP as the criteria, same thing. not sure what will work or maybe a different formula?

Thanks in advance!