# SUMPRODUCT or SUMIF using an array for criteria

1. ## SUMPRODUCT or SUMIF using an array for criteria

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?

2. ## Re: SUMPRODUCT or SUMIF using an array for criteria

Try this formula

=SUMPRODUCT(SUMIF('sales worksheet'!\$B\$6:\$B\$431,CC11:CJ11,'sales worksheet'!\$W\$6:\$W\$431))

3. ## Re: SUMPRODUCT or SUMIF using an array for criteria

Ah! Worked like a charm! Didn't think about using both in the same formula. Thanks again!

4. ## Re: SUMPRODUCT or SUMIF using an array for criteria

oh, so one caveat... what if the columns in row 11 can vary, such as up to 30 or more columns, e.g., DD11? is there a way for the formula to recognize the last column? If not, I'll just set it to go out a certain number of columns

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1