# SUMIF workaround SUM(IF

#### Flazz

##### New Member
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

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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### jasonb75

##### Well-known Member
You can't use wildcards in that type of formula. Try
Excel Formula:
``=SUMPRODUCT(--ISNUMBER(SEARCH(B1,'F:\[test1.xlsx]Sheet1'!\$A\$2:\$A\$6)),'F:\[test1.xlsx]Sheet1'!\$B\$2:\$B\$6)``

#### Flazz

##### New Member
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.

Replies
1
Views
169
Replies
3
Views
441
Replies
15
Views
736
Replies
0
Views
84
Replies
4
Views
164

1,181,635
Messages
5,931,127
Members
436,776
Latest member
kranda

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back