The way to sum the same codes in Excel

Excel has developed a powerful and useful tool for adding values ​​that satisfy one or more conditions, the SUMIF and SUMIFS functions. But which formula to find the sum of values ​​with cells with the same code snippet? This is a very interesting question that many readers send to Dexterity Software. Find out how in the following article.

Use the SUMIF function and sub column

Consider the following specific example:

Step 1: Use an extra column to identify the rows whose characters meet the conditions.

(1) You create an extra column with the heading Shirt and Women .

(2) On the Shirt condition column, specifically cell H3, type the formula: H3 = IFERROR (SEARCH ("Shirt", D3), 0) . This means that Excel will look in cell D3 for any character that matches the Shirt and return its position in the string. If there are no characters that meet the condition, it will return position 0. Copy the formula for all the cells in the column you get the following result:

(3) Similarly with the Women column , you change the condition from Shirt => Female and get the result:

Thus, if the rows have code that satisfies the condition, the extra column will have a value greater than zero.

Step 2: Add rows with a value greater than zero in the secondary column with the SUMIF function.

To add Sales by product condition as Shirt , in cell K3 you type the formula: K3 = SUMIF (H3: H12, "> 0", G3: G12) .

Similar to cell K4, type the formula K4 = SUMIF (I3: I12, "> 0", G3: G12) to add Sales to meet the condition that the product contains a female character code .

Note : With the above method, Excel does not distinguish uppercase and lowercase characters.

Use the SUMIF function and wildcard characters

If you do not want to use the extra column method, you can use the asterisk (*) as a wildcard for any character in the formula:

= SUMIF ( conditional reference array , "*" & " search string " & "*", area to be summed ). You change the italic item that fits your report.

With the same example as above, Dexterity type the formula: K3 = SUMIF (D3: D12, "*" & "Shirt" & "*", G3: G12), K4 = SUMIF (D3: D12, "* "&" female "&" * ", G3: G12).

And the result is:

Note: To be able to copy formulas, you should use absolute position to fix the reference array and the area to be summed. You replace the above formula with
K3 = SUMIF ($ D $ 3: $ D $ 12, "*" & J3 & "*", $ G $ 3: $ G $ 12). Where J3 is the cell containing the string to search. The result of the cell does not change.

Use the SUMPRODUCT function

The SUMPRODUCT function is one of Excel's special array functions.

SUMPRODUCT uses the range of cells as its argument together the items in the arrays, and then sums the results.

For the above example, enter the formula: K3 = SUMPRODUCT ((G3: G12) * (IFERROR (SEARCH ("shirt", D3: D12), 0)> 0)), then press Ctrl + Shif + Enter .

1. With the above formula, the IFERROR (SEARCH ("shirt", D3: D12), 0) function will look up the cells in column D with a shirt character and return a value greater than zero, otherwise. will return 0.
2. When you put the above formula into the SUMPRODUCT function, Excel will compare that result with> 0 or not, and sum the total sales in the Sales column (Column G) corresponding to the value above> 0.

Note: Although the SUMPRODUCT formula is an array formula, it means you don't have to apply Ctrl + Shift + Enter in the usual case. However, in this case, you must use Ctrl + Shif + Enter or you will not get the correct results.

By doing the same thing for cell K4, Dexterity Software finds the total sales that satisfy items with the female characters . Good luck!