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!
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

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,458

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,201

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,201
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,387
Members
412,590
Latest member
Velly
Top