10

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!

Improve this question
1
  • I'm not near excel right now, but would range.find do the trick? I believe it returns a range if found and null if not.
    – withoutIf
    Commented Feb 8, 2015 at 23:44

2 Answers 2

Reset to default
16

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)

enter image description here

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.

enter image description here

Improve this answer
0
1

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

Improve this answer
1
  • I know it's a long time ago but I still wonder: Why did you even post that ten months after my answer? That formula is an option in my answer, too, screenshot and all. Don't people read things anymore?
    – teylyn
    Commented Feb 15, 2017 at 19:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.