Summing the matches of one column only if its corresponding description matches a list

Hi, welcome to the forum

Try this...
=SUMPRODUCT(--(ISNUMBER(MATCH(COMPARE!$A$1:$A$14,SOURCE!$A$1:$A$9,0))),(COMPARE!$B$1:$B$14))

haha thanks for the kind words, glad it worked for you

EXAMPLE2.xlsxYour solution worked perfectly for the example I gave, so thank you for that very much. I would like to add a bit more complexity and have gotten close to the answer but am coming up short. I need there to be one more criteria to test against before summing up the numbers. I've tried adding another array to your solution but am not getting the right answer.

OK, shouldnt be too hard. You just need to add it in the same format...
=SUMPRODUCT(--(ISNUMBER(MATCH(COMPARE!$A$1:$A$14,SOURCE!$A$1:$A$9,0)))*(date_range=TODAY),(COMPARE!$B$1:$B$14))
=SUMPRODUCT(--(ISNUMBER(MATCH(COMPARE!$A$1:$A$14,SOURCE!$A$1:$A$9,0)))*--(name_range="Bobby"),(COMPARE!$B$1:$B$14))
or some such.

What are you adding, and what did you try?

	B	C	D	E
5	55555	66666	88888	99999
6	60	20	50	40
7	170
8
9	60	20	50	40

B6=SUM(SUMIFS(COMPARE!$B$1:$B$14,COMPARE!$C$1:$C$14,SUM!B$5),COMPARE!$A$1:$A$14,SOURCE!$A$1:$A$9)
copied across
If you want to sum all values that match all criteria...
B7=SUM(SUMIFS(COMPARE!$B$1:$B$14,COMPARE!$C$1:$C$14,{55555,66666,88888,99999}),COMPARE!$A$1:$A$14,SOURCE!$A$1:$A$9)

Or, you can stick with the SP version, bit I can only get it to calc per each type...
B9=SUMPRODUCT(--(ISNUMBER(MATCH(COMPARE!$A$1:$A$14,SOURCE!$A$1:$A$9,0))),(COMPARE!$B$1:$B$14),--(COMPARE!$C$1:$C$14=SUM!B$5))
copied across

sorry about that. I think this does it...
=SUMPRODUCT(--(ISNUMBER(MATCH(COMPARE!$A$1:$A$14,SOURCE!$A$1:$A$9,0))),(COMPARE!$B$1:$B$14),--(ISNUMBER(MATCH(COMPARE!$C$1:$C$14,SUM!B5:E5,0))))

Are there any #VALUE! errors in any of the referenced ranges?

Are all the MATCH/SUM ranges the same size?

COMPARE!$A$1:$A$14
COMPARE!$B$1:$B$14
COMPARE!$C$1:$C$14

Those are all the same size but what about the formula in your real file?

I figured it out! It took me FOREVER to dig through everything. Apparently the newly added criteria was referencing a cell that contained a number, but this number, for whatever reason, had a hidden apostrophe at the beginning! Once i updated my lookup criteria by adding an apostrophe in front, the formula instantly worked!

Now why in the world would there be a hidden apostrophe?! Curse the Punctuation Gods!!! No, really, why didn't excel show me there was an apostrophe in my source data???

ALSO, I've notice very frustrating inconsistencies when referencing an array like the newly added criteria in example4. Why is it sometimes you must do this: {55555,66666,88888,99999} instead of the easier: (B5:E5) ?

Thank you in advance, you all have a GREAT support forum here!

Originally Posted by bishop-jese-erl

Why is it sometimes you must do this: {55555,66666,88888,99999} instead of the easier: (B5:E5) ?

You shouldn't have to use the array constant: {55555,66666,88888,99999}.

However, if using a range reference like B5:E5 then this must be used with functions that support array referencing or the formula must be array entered.