How to Sum Same Codes in Excel
How to Sum Same Codes in Excel: Summing the same codes in Excel is a task that many people encounter in their work, especially when working on statistics/summaries. Whether you need to sum the quantity or total amount of specific product codes, Excel provides several functions that can help you accomplish this task quickly and accurately. In this article from Buffcom.net, we will show you how to use the SUMIF and SUMPRODUCT functions to sum the same codes in Excel.
For example, we have a data table as shown in the figure below. We need to sum the total amount of all tea products.
How to Sum Same Codes in Excel
1. Use SUMIF function
As you can see in the figure above, there are 3 types of tea: peach tea, lemon tea, and kumquat tea. To sum the total amount of each tea type, we need to use the SUMIF function combined with the asterisk (*) as a wildcard character according to the formula:
=SUMIF(range of condition reference, “”& “search string”&””, range to sum)
Applying this formula to the spreadsheet, we will have the following formula to sum the total amount of each tea product:
=SUMIF(B2:B10;””&”Tea”&””;E2:E10)
How to Sum Same Codes in Excel
2. Use SUMPRODUCT function
Another way to sum the total amount of tea products is to use the SUMPRODUCT function combined with the IFERROR and SEARCH functions. Enter the formula below into Excel and then press the Ctrl + Shift + Enter key combination.
=SUMPRODUCT((E2:E10)*(IFERROR(SEARCH(“Tea”;B2:B10);0)>0))
With the above formula, the IFERROR(SEARCH(“Tea”;B2:B10);0) function will search for cells in column B that contain the string “Tea” and return a value greater than 0, or return 0 if not found. When this formula is entered into the SUMPRODUCT function, Excel will compare the result to >0 and sum the corresponding total amount in column E.
Although the SUMPRODUCT formula is an array formula, which means you don’t have to use the Ctrl + Shift + Enter key combination in regular cases, for this case, you must use this key combination or you will not get accurate results.
How to Sum Same Codes in Excel
In conclusion, summing the same codes in Excel is a common task in many workplaces. By using the SUMIF or SUMPRODUCT functions, you can quickly and accurately sum the quantity or total amount of specific codes. These functions can save you time and effort when working with large data sets, making them essential tools for data analysis in Excel.
We hope that this guide has been helpful to you and that you can use these functions to simplify your work. As always, practice makes perfect, so don’t be afraid to experiment with different formulas to find the best one for your needs.
