Geek Answers Handbook

Excel SUMIF when another cell contains text

So, for example, for 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 sums if the description of this sum contains "**" (or some other denoting text). In this particular example, I need a formula that returns 34, since only Item2 and Item4 contain "**".

I tried using 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!

+7
excel-formula
source share
2 answers

An asterisk is a wildcard character that can be used in Sumif (), so you may need to change the designating text to some other characters, such as @@. Then this formula will work:

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

enter image description here

If you want to keep the stars, the formula gets a little higher.

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

The two middle stars are shielded by the tilde.

enter image description here

+12
source share

You can avoid the wildcard character and turn it into a literal * by prefixing its expanded dash (tilde, ~ ) and leave your data unchanged:

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

IMO is worth it because astrisks are relatively "elegant."

+1
source share

More articles:


All Articles