Sumproduct using range as criteria to compare another range

HI,

I'm trying to use sumproduct across workbooks, one of them will be closed. Trying to sum a column matching, certain keyword list. Actually, criteria keywords
should be an exclusion. In other words, I would like to sum column with rows which do not include the list of keywords. I'm using sumproduct vs SUMIF because of lack of connection with closed workbooks when working with SUMIFS.
I would like to maintain keyword list in a separate column, which will be periodically updated. But I do not want to update numerous rows of SUMPRODUCT formulas, every time there is a new keyword.
I'm not very proficient with VBA, but I would consider VBA code as an option as well.
Any help is much appreciated.


here is the working formula I have:

Please Login or Register  to view this content.

But here is what ideally I would like to have:

Please Login or Register  to view this content.

List of keywords would be in separate column:
Exclusion keywords which has KEYWORDS as name range:
Fee
Subscription Fee
Boat Fee
Other
Travel
serving

Try

=SUMPRODUCT(--(ISNA(MATCH('[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$R$2:$R$500,KEYWORDS,0))),'[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$J$2:$J$500)

Jonmo1 I cannot THANK YOU enough. This worked. I was trying something similar, but I was listing KEYWORDS first and it was giving me error. This did the trick.

I was trying below code and I was getting value error:

Please Login or Register  to view this content.

but moving KEYWORD as lookup-array did the trick:

Please Login or Register  to view this content.

thank you once again.

You're welcome.

I was also thinking of an alternate way, with a kind of reverse logic..

Instead of summing the ones that do not = keywords...
Sum ALL of them, and subtract the ones that DO = keywords...

=SUM('[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$J$2:$J$500)-SUMPRODUCT(SUMIF('[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$R$2:$R$500,KEYWORDS,'[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$J$2:$J$500))

I know this is probably more work than the other way, but thinking further..
You may want to have (or maybe you already do have) a formula that gives the overall sum of J, regardless of the values in R..
And then maybe you want to have 2 more formulas, 1 to show the ones that DO = Keywords, and another to show the ones that do NOT = keywords..

In this case, then this logic is actually quite usefull..

In A1, get the overall sum of J regardless of values in R
=SUM('[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$J$2:$J$500)

Then in A2, get the value of the ones that DO = keywords
=SUMPRODUCT(SUMIF('[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$R$2:$R$500,KEYWORDS,'[Global Chart 2016 1.1.16.xlsx]Raw Data 2016'!$J$2:$J$500))

Then in A3, the the values of the ones that do NOT = Keywords
=A1-A2

Jonmo1, that is a wonderful idea. I will definitely be using the alternative approach.

Many many thanks

You're welcome.