Sumproduct with contains

[erniepoe]

Hi all,

I'm trying to use Sumproduct but when i only have a partial match.

In this case, the first part of the formula are months Jan-Dec, and A1 is whatever month you are looking at.

In column A on the 300-STAT-A tab, i want to add all the rows that contain the word food. I have tried updating the formula with both ="*food*" and ="%food%" as you see below, but no luck.

=SUMPRODUCT(('[January 2020 Actuals + Budget.xlsx]300 - STAT-A'!$B$5:$N$5=A1)*('[January 2020 Actuals + Budget.xlsx]300 - STAT-A'!$A:$A="%FOOD%"),'[January 2020 Actuals + Budget.xlsx]300 - STAT-A'!$B:$N)

Have also been struggling with combining Sumif with index match, but no dice on either.

Any help? Thanks so much.

 

[Eric W]

Try:

=SUMPRODUCT(('300-STAT-A'!B5:M5=A1)*ISNUMBER(SEARCH("food",'300-STAT-A'!A6:A14)),'300-STAT-A'!B6:M14)

You can't use wildcards in SUMPRODUCT, but you can use SEARCH and see if the substring is found with ISNUMBER. If you want to use wildcards, then SUMIFS could work:

=SUMIFS(INDEX('300-STAT-A'!B6:M14,0,MATCH(A1,'300-STAT-A'!B5:M5,0)),'300-STAT-A'!A6:A14,"*food*")

SUMIFS also is "aware" of the last row, so it's more efficient to use if you want to use whole column references:

=SUMIFS(INDEX('300-STAT-A'!B:M,0,MATCH(A1,'300-STAT-A'!B5:M5,0)),'300-STAT-A'!A:A,"*food*")

 

[erniepoe]

Thank you so much Eric, that worked perfectly!

 

[Eric W]

Happy to help!