CODEWITHSUNDEEP
excelvbasumifs
s.newave
s.newave

Reputation: 455

How to use the SUMIF function in Excel, or something similar, to count the occurrence of a *word* within a given range?

I'm trying to use the SUMIF function in Excel, or anything at this point, to sum up the occurrences of a given word within a given range.

For example:

  A               B
men              500
groomsmen        500
jolly men        500

For the table above I would like to create a SUMIF, or equivalent, function that returns 1000. I've tried 

=SUMIF($A$1:$A$3,"*"&A1&"*",$B$1:$B$3)

However that returns 1500 as it matches groomsmen. I've also tried 

=SUMIF($A$1:$A$3,A1,$B$1:$B$3)+SUMIF($A$1:$A$3," "&A1,$B$1:$B$3)+SUMIF($A$1:$A$3,A1&" ",$B$1:$B$3)

But that doesn't seem to work as it seems that Excel trims the space character.

Also tried using SUMIFS but that didn't work because the criteria is AND'd together.

I even tried adding regexp support through some addon however the SUMIF function doesn't seem to be that flexible in the criteria it takes so that didn't work either.

Any ideas?

Upvotes: 0

Views: 331

Answers (1)

Juan Avalos
Juan Avalos

Reputation: 126

It's not clear what you want, but here's a formula that returns 1000 for those values:

=SUMIF($A$1:$A$3,A1,$B$1:$B$3)+SUMIF($A$1:$A$3,"* "&A1,$B$1:$B$3)+SUMIF($A$1:$A$3,A1&" *",$B$1:$B$3)

Inserting a space before the value will look for an exact match of " men", by adding the asterisk before, will look for anything that ends in " men", the opposite goes for the third part of the formula, looks for anything that starts with "men ".

Upvotes: 2

Related Questions