# sumif "like"

#### sarahrosenberg

##### Board Regular
I tried to search the forum for an answer to this question, but couldn't figure out exactly what to search for.

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.)

### 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
Try using the * wildcard. For example:

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

Hope that helps!

#### PA HS Teacher

##### Well-known Member
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)

#### sarahrosenberg

##### Board Regular
Why do you use sumproduct instead of sumif? It works the same in this example, right?

#### Brian from Maui

##### MrExcel MVP

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.

#### sarahrosenberg

##### Board Regular
Do you guys realize how smart you make me look at work?

thanks!

##### MrExcel MVP

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
Of course you've got to be careful you don't include Arkansas figures when you're trying to SUM Kansas.......

#### sarahrosenberg

##### Board Regular
Ahhh...great point! Gold star!

##### MrExcel MVP
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)

Replies
3
Views
162
Replies
1
Views
31
Replies
0
Views
70
Replies
0
Views
275
Replies
4
Views
207