Excel SUMIF when another cell contains text

Question

So for example purposes, I have the following table:

|    |     A      |    B     |
|    |------------|----------|
|  1 |Description |Amount    |
|  2 |------------|----------|
|  3 |Item1       |      5.00|
|  4 |Item2**     |     29.00|
|  5 |Item3       |      1.00|
|  6 |Item4**     |      5.00|
|  7 |------------|----------|
|  8 |Star Total  |     34.00|
|  9 |------------|----------|

I want to create a formula in B8 that calculates the sum of the amounts if the description of that amount contains "**" (or some other denoting text). In this particular example I would like a formula that returns 34 since only Item2 and Item4 contain "**".

I tried to use something like this, but it only worked based on the value in A3:

=SUMIF(A3:A6, ISNUMBER(SEARCH("**", A3)), B3:B6)

Any suggestions would be appreciated!

Answer 1

The asterisk is the wildcard symbol that can be used in Sumif(), so you may want to change the denoting text to some other symbols, for example @@. Then this formula will work:

=SUMIF(A2:A10,"*@@*",B2:B10)

If you want to keep the asterisks, the formula gets a bit curlier.

=SUMIF(A2:A10,"*~*~**",B2:B10)

The two middle asterisks are escaped with the tilde character.

Answer 2

You can escape the wildcard character and turn it into a literal * by prefixing it with a swung dash (tilde, ~) and so leave your data unchanged:

=SUMIF(A2:A7,"*~*~*",B2:B7)  

IMO worthwhile because astrisks are relatively 'elegant'.