# SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

1. ## SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

My data would be arranged something like this:

A B C D

ab 1 2 3
cd 4 5 6
ef 7 8 9
gh 2 5 8

(Text in column A and numbers in columns B,C and D)

I would like to sum for "ab" an "cd" across all columns B,C and D. "ab" and "cd" would be linked to cells that I would like to keep variable i.e. I DO NOT want to hard code them in the formula. So if i enter "ab" in "cd" in the linked cells, I should get (1+2+3)+(4+5+6)=21 as the answer. As another example, if i change the linked cells to "gh" and "cd", I should get (2+5+8)+(4+5+6)= 30 as the answer. Please help.

Note: I have tried using sumifs with multiple criteria in the same column, but apparently it cannot handle multiple columns (i cannot add a "total" column in the end because the sumrange would be dynamic). Sumproduct can handle multiple columns but then I would have to hard code the criteria, which I cant (as the criteria would be dynamic as well and there would be more than 50 criteria in some cases).

2. ## Re: SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

Please post an excel file with these data and some examples of the desired results, to make clear what you want exactly.

3. ## Re: SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

Assuming criteria cells are F2 and G2 try this version

=SUMPRODUCT(((A2:A10=F2)+(A2:A10=G2))*B2:D10)

4. ## Re: SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

I have attached a file for reference.

@dannylonglegs
I would like to specify F2 and G2 as an array as in:
=SUMPRODUCT((A2:A10=F2:G2)*B2:D10)

because there would be more than 50 criteria in some cases. But specifying the criteria cells as an array does not work as it searches for both cells as a single item (in the exact order) within column A, rather than searching for them as two separate items. In a nutshell, I would like to tell excel that I want you to look for F2:G2 in A2:A10, where the value can be EITHER F2 or G2 and return TRUE or FALSE. So if I enter "cd" and "gh" in F2 and G2, it should return FALSE, TRUE, FALSE, TRUE in A2:A5. Hope I was clear enough.

5. ## Re: SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

SOLVED.
Hey I did something rather unusual and got it working. I used the MATCH function and switched the lookup value with the lookup array. Assuming F2:F4 contain the lookup values of "ab", "cd" and "gh" respectively, I enter:

=IF(ISNUMBER(MATCH(A2:A5,F2:F4,0))=TRUE,1,0)

I get an array: {1,1,0,1} in A2:A5 which is exactly what I wanted! Now I can simply sumproduct this array with B2:D5 to get the desired results.

Regardless, thanks a lot for all your help.

6. ## Re: SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

You can incorporate ISNUMBER(MATCH into a SUMPRODUCT formula to do that all in one go if you want, i.e.

=SUMPRODUCT(ISNUMBER(MATCH(A2:A5,F2:F4,0))*B2:D5)

7. ## Re: SUMPRODUCT: Multiple criteria in same column and criteria NOT hard coded

Yeap, thats what I figured. Thankyou!

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