SUMIF workaround SUM(IF

Flazz

New Member
Joined
Jun 16, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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
 

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
Joined
Dec 30, 2008
Messages
15,396
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

Flazz

New Member
Joined
Jun 16, 2021
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top