Return and sum multiple values using a wildcard criteria

Question

I'm trying to create a complex formula that does the following:

1. Looks up data in column A of another sheet using a "contains" criteria (xxxx&"*")
2. Returns all matching values from a specific column (column B)
3. Sums the returned values to provide a total

I've attached a view of some sample data as an example of the data-set I'm working with:

For instance, I may want to show the total number of apples, regardless of their colour (8), in a single cell.

I've tried a number of things (VLOOKUP, INDEX, MATCH) but I can't seem to get it 100% right.

Answer 1

The solution is very simple. You need to use the SUMIF() function.

With two worksheets set up like this:

  

Enter the following formula in cell B2 of Sheet1:

=SUMIF(Sheet2!$A$2:$A$5,A2&"*",Sheet2!$B$2:$B$5)

Note that this formula actually performs a "starts with".

The "contains" formula needs another wildcard:

=SUMIF(Sheet2!$A$2:$A$5,"*"&A2&"*",Sheet2!$B$2:$B$5)

---

Wildcards Explanation

There are three wildcard characters that can be used in some Excel functions:

• ? - matches any one character
• * - matches zero or more characters
• ~ - "escapes" the following character, e.g. ~?,~*, and ~~ will match the literal characters ?,*,and ~ respectively. Note that ~ followed by any other character will only match that character, not a tilde followed by that character

The most useful functions allowing the usage of wildcards are:

• SEARCH()
• MATCH()^*
• VLOOKUP() & HLOOKUP^*
• SUMIF(), SUMIFS(), COUNTIF(), COUNTIFS(), AVERAGEIF() & AVERAGEIFS()

^* Wildcards can only be used if the third argument of MATCH() is a 0 (exact match) or the fourth argument of VLOOKUP()/HLOOKUP() is TRUE (exact match)