sumif "like"

sarahrosenberg

Board Regular
Joined
Aug 27, 2002
Messages
190
I tried to search the forum for an answer to this question, but couldn't figure out exactly what to search for. :unsure:

I want to do a sumif, but instead of searching for an exact cell reference, I want to search for a "like" cell reference. For example

Illinois South 5
Illinois North 6
Missouri West 8
Ohio South 9
Illinois West 6

I want to do a sumif that would pull all of the Illinois references eventhough there are other words in the cell reference. (My real data isnt' quite as uniform, so a text-to-column won't work.)

Help please!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Try using the * wildcard. For example:

=SUMIF(A26:A30,"Illinois*",B26:B30)

Will sum B26:B30 if A26:A30 start with the string "Illinois".

Hope that helps!
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
If you have Entries that do not start with Illinois, for example if they started with a space, you could use:

=SUMPRODUCT(--ISNUMBER(FIND("Illinois",A26:A30)),B26:B30)
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459

ADVERTISEMENT

sarahrosenberg said:
Why do you use sumproduct instead of sumif? It works the same in this example, right?

Yes, but......

Taz's Sumif will work if Illinois comes first in any text string, as in IllinoisXXXX, but not XXXXIllinois.

PA's is case sensitive so illinoisxxx of xxxillinois will not "work" but Illinoisxxx or XXXIllinois will.

If Illinois is anywhere part of a string, use PA's Find or change to Search. If Illinois is found only at the beginning of a string use Taz's.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210

ADVERTISEMENT

Brian from Maui said:
sarahrosenberg said:
Why do you use sumproduct instead of sumif? It works the same in this example, right?

Yes, but......

Taz's Sumif will work if Illinois comes first in any text string, as in IllinoisXXXX, but not XXXXIllinois.

PA's is case sensitive so illinoisxxx of xxxillinois will not "work" but Illinoisxxx or XXXIllinois will.

If Illinois is anywhere part of a string, use PA's Find or change to Search. If Illinois is found only at the beginning of a string use Taz's.

=SUMIF(A26:A30,"*Illinois*",B26:B30)

will take up any Illinois. So, no need for SumProduct...
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Of course you've got to be careful you don't include Arkansas figures when you're trying to SUM Kansas....... :)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
barry houdini said:
Of course you've got to be careful you don't include Arkansas figures when you're trying to SUM Kansas....... :)

Correct afterwards if needed...

Sum(Kansas)-Sum(Arkansas)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,835
Messages
5,855,909
Members
431,772
Latest member
dannyboi1

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