CODEWITHSUNDEEP
excel-formula
David
David

Reputation: 137

Excel SUMIF when another cell contains text

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!

Upvotes: 10

Views: 33133

Answers (2)

pnuts
pnuts

Reputation: 59442

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

Upvotes: 1

teylyn
teylyn

Reputation: 35900

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

Upvotes: 16

Related Questions