I have a collection of text strings that each have an associated numerical value. I need to sum the associated numerical values for those entries that "qualify". A text entry qualifies by containing any one or more designated target strings. An entry can potentially contain several of the target strings, or a target string more than once. However, I only want to sum the associated value once for the entry if the entry qualifies by containing any match to any of the targets or combinations of targets.

For instance, say cells A1:A3 respectively contain `apple`

, `banana`

, `pear`

, and B1:B3 each contain the number `1`

. My search targets are `a`

and `p`

. All three text entries qualify because they each contain at least one instance of at least one of the targets. Summing the associated values in column B should return a result of `3`

.

I have attempted this using SUMIF and wildcard targets. My formula for this example is:

```
=SUM(SUMIF(A:A, {"*a*", "*p*"}, B:B))
```

However, this double-counts entries that match more than one target. In this case, all three contain `a`

and two also contain `p`

, so it produces a sum of `5`

.

How can I accomplish this without double-counting entries?

`SUMIF`

does not count text. And with an array constant for the criteria, you won't get a good result with your formula as you show it. Also, variables cannot be used in an array constant. Providing a real example of your data, formula, actual and expected results would be more useful. Add this by editing your question, not by adding a comment. – Ron Rosenfeld Sep 1 '16 at 0:35