sumif "like"

[sarahrosenberg]

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

Help please!

 

[Tazguy37]

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]

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]

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

 

[Brian from Maui]

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]

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



thanks!

 

[Aladin Akyurek]

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]

Of course you've got to be careful you don't include Arkansas figures when you're trying to SUM Kansas.......

 

[sarahrosenberg]

Ahhh...great point! Gold star!

 

[Aladin Akyurek]

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)