1

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?

  • 1
    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
  • I find this question to be very interesting, I'm hoping some excel wizards show up and knock this out of the park. – Raystafarian Sep 1 '16 at 19:12
  • I made an edit to your question and what I think you're trying to ask, if not feel free to roll back – Raystafarian Sep 1 '16 at 19:15
  • @Raystafarian I do not understand your edit. Without wildcards, I thought your example would return 0. Are you trying to provide an example of where the count succeeds? I was trying to provide an example of the failure case. – Ninjakannon Sep 1 '16 at 19:21
  • It's useful to provide a successful example. Are the wildcards in the search or are you searching in "wildcard" cells - that was unclear. – Raystafarian Sep 1 '16 at 19:22
1

It's more flexible to have your criteria in actual cells within the worksheet, rather than hard-coded in-formula.

If you use a vertical, contiguous range of cells (e.g. H1:H2) to this end, and assuming that Range is a vertical range, you can use this array formula**:

=SUM(IF(MMULT(0+ISNUMBER(SEARCH(TRANSPOSE(Criteria),Range)),ROW(Criteria)^0)>0,Values))

If you insist on having the criteria in-formula, then:

=SUM(IF(MMULT(0+ISNUMBER(SEARCH({"p","a"},Range)),{1;1})>0,Values))

Regards

  • 1
    This solution works, but for anyone who isn't an Excel expert, they would have a hard time understanding how or why it works, or adapting it to a specific problem. To be useful, it really needs an actual working example and some explanation. – fixer1234 Jun 28 at 0:31
1

Here's a relatively simple solution. With associated values all 1, it produces the desired result of 3, but I assigned different values to demonstrate that the correct values are selected, and included a non-matching entry for good measure.

enter image description here

The list of entries is in column C and their associated values in column D. The result is in E1.

Only certain functions can use wildcards, so this uses SEARCH for the target string.

The typical method of handling multiple OR criteria is adding the results of each test. However, that double-counts when the items can meet multiple criteria. To solve that, the aggregated criteria tests are checked to see if the sum is greater than zero, and that is what is used with the associated value.

Handling the target search strings as an array gets complicated, because the common functions used for these kinds of formulas calculate a result for the entire array before applying it to the next term. So I treated each criterion separately. For more criteria, just add another ISNUMBER(SEARCH("target",range)) for each one inside the parentheses before the >0 test.

SUMPRODUCT does the array-style calculations with a normal, non-array formula.

The formula in E1 is:

=SUMPRODUCT(((ISNUMBER(SEARCH("p",C1:C4))+ISNUMBER(SEARCH("a",C1:C4)))>0)*D1:D4)
  • Thanks for your answer! This is really helpful. Is it possible to generalise your query for a non-trivial number of search terms, i.e. to avoid duplicating the repetitions of ISNUMBER and the sub-terms therein? – Ninjakannon Jun 28 at 10:32
  • @Ninjakannon, unfortunately, this approach requires a formula entry for each target. If you have a large number of them, XOR LX's answer will handle it like a target list, so it's more scalable. – fixer1234 Jun 28 at 10:45
0

I really thought this would be a SUMPRODUCT(-- formula, but I can't get one to work. This should work though -

=SUM(IF(FREQUENCY(IF(NOT(ISERR(SEARCH({"d","g"},A2:A10))),ROW(A2:A10)),ROW(A2:A10))>0,1))

Where the {"d","g"} will be your array of search strings.

It's an array formula, so once you put it in you have to hit ctrl + shft + entr and curly brackets should show up in the formula bar around the entire function.

Note it will only work if you're searching a single column.

It's possible this won't work as well, considering wildcards. Maybe regex is what you need?

  • Seven layers can't be enough. Deeper! Deeper! – JaredT Sep 1 '16 at 20:27
  • I don't think this accomplishes the challenge in the question. It doesn't sum the associated values, and I don't think you can get there by treating the search terms as an array, at least without a matrix approach like in XOR LX's answer. – fixer1234 Jun 28 at 0:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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