Buzz
Excel has developed powerful and useful tools to sum values satisfying one or multiple conditions, such as the SUMIF and SUMIFS functions. But what formula should be used to find the sum of values with cells containing similar codes? This is a very interesting question that many readers have sent to Software Tips. Let's explore how to do it in the following article.
Using the SUMIF Function and a Helper Column
Consider the following specific example:
Step 1: Use a helper column to identify rows with characters that satisfy the condition.
(1) Create a helper column with headers Shirt and Female.
(2) In the Shirt condition column, specifically cell H3, enter the formula: H3 =IFERROR(SEARCH('Shirt',D3),0). This means Excel will search cell D3 for any characters satisfying Shirt and return the position of that character in the string. If no characters satisfy the condition, it will return position 0. Copy the formula for all cells in the column to get the following result:
(3) Similarly, for the Female column, replace the condition from Shirt => Female and obtain the result:
Thus, rows with codes satisfying the condition will have a value greater than 0 in the helper column.
Step 2: Sum the rows with values greater than zero in the helper column using the SUMIF function.
To sum the Revenue based on the product condition being Shirt, in cell K3, enter the formula: K3 =SUMIF(H3:H12,'>0',G3:G12).
Similarly, for cell K4, enter the formula K4 =SUMIF(I3:I12,'>0',G3:G12) to sum Revenue satisfying the condition of the product containing the character code for female.
Note: With this method, Excel does not distinguish between uppercase and lowercase characters.
Using the SUMIF Function and Wildcard Characters
If you don't want to use the method of adding a helper column, you can use a combination with the asterisk () as a wildcard character for any character according to the formula:
=SUMIF(condition reference array,''&'search string'&'*',range to sum). You adjust the italicized parts according to your report.
With a similar example as mentioned in the Software Tips, enter the formulas: K3 =SUMIF(D3:D12,''&'Shirt'&'',G3:G12), K4 =SUMIF(D3:D12,''&'female'&'',G3:G12).
And the results obtained are:
Note: To copy the formulas, you should use absolute references to fix the reference array and sum range. Replace the formula with
K3=SUMIF($D$3:$D$12,''&J3&'',$G$3:$G$12). Where J3 is the cell containing the search string. The result of the calculated cell remains unchanged.
Using the SUMPRODUCT Function
SUMPRODUCT is one of Excel's special array functions.
It utilizes ranges of cells as its arguments, multiplies corresponding items in arrays together, and then sums the results.
For the example above, input the formula: K3 =SUMPRODUCT((G3:G12)*(IFERROR(SEARCH('shirt', D3:D12), 0)>0 )), then press Ctrl +Shif + Enter.
- With the above formula, the IFERROR(SEARCH('shirt', D3:D12), 0) function searches cells in column D for the string shirt and returns a value greater than 0 if found, otherwise returns 0.
- When placing this formula within the SUMPRODUCT function, Excel compares the result if it's >0 and sums the revenue in the Revenue column (Column G) corresponding to values greater than 0.
Note: Although the SUMPRODUCT formula is an array formula, meaning you don't need to use the Ctrl + Shift + Enter combination in typical cases. However, in this scenario, you must use the Ctrl + Shift + Enter combination, otherwise, you won't get accurate results.
With a similar approach to cell K4, Software Tips finds the total revenue for items containing the character female. Wishing you all success!
