SUMIF workaround SUM(IF

[Flazz]

Hello, I am hoping you can help.
Im trying to convert a SUMIF formula in excel 2010 to a SUM(IF.

The original formula is

=SUMIF('F:\[test1.xlsx]Sheet1'!$A$2:$A$6,"*"&B1&"*",'F:\[test1.xlsx]Sheet1'!$B$2:$B$6)

Evaluate the formula results in
B1=Bob
It then checks for the word appearing in the range specified
for clarification

bob is great	1​
BOb is pants	2​
boB is the best	6​
cats cool	7​

this results in the correct answer of 9

however if test1.XLSX isn't open I get a #value error

So I searched and found that "Sum(IF" is a good workaround so the test1.XLSX could remain closed

here is my workaround


=SUM(IF('F:\[test1.xlsx]Sheet1'!$A$2:$A$6="*"&B1&"*",'F:\[test1.xlsx]Sheet1'!$B$2:$B$6,0))

But it doesn't work, and I don't understand why.
it returns the result 0 (no matter if I use enter or CRTL, Shift and enter)

Evaluation shows

SUM(IF{"bob is great";"Bob is pants";"BoB is the best";"cats cool";0}="*Bob*",'F:\[test1.xlsx]Sheet1'!$B$2:$B$6,0))

all seems okay

but next step reads

SUM(IF{FALSE;FALSE;FALSE;FALSE;FALSE}",'F:\[test1.xlsx]Sheet1'!$B$2:$B$6,0))

then as it cant seem to see the words "Bob" comes back with 0

I could really do with some help on this. Two days is too long to be working on this.

Thanks for any assistance you can provide

 

[jasonb75]

You can't use wildcards in that type of formula. Try

=SUMPRODUCT(--ISNUMBER(SEARCH(B1,'F:\[test1.xlsx]Sheet1'!$A$2:$A$6)),'F:\[test1.xlsx]Sheet1'!$B$2:$B$6)

 

[Flazz]

That worked Perfectly,
Previously I had used SEAC and ISNUMBER trying to get this to work. combining them with SUMPRODUCT .... inspired.

Thank you so much.