The first column "Product" has a list of items (repeated few times) followed by number of units (or pieces) sold in the second column.
Column "E" shows a list of unique items (extracted from column "A") followed by the SUM of units sold for each item in column "F".
There are many different ways you can achive this. However, I did this using two different formulas.
Get distinct or unique items from the list
First, lets get the unique items from the list.
The column "Product" has a list of items repeated few times. So, first I'll get the "distinct items from the list" in the "E" column.
Copy the below formula in "E2" column and press Ctrl+Shift+Enter" keys.
Note: Ctrl+Shift+Enter command convert the data into an array format in Excel. The formula (if there are no errors) will be encapsulated inside curly braces { }.
=IFERROR(INDEX($A$2:$A$10, MATCH(0,COUNTIF($E$1:E1, $A$2:$A$10), 0)),"")
Set focus on E2 column and drag it down.
Note: If you have access to Excel for Microsoft 365, Excel 2021 or Excel for the Web, you can use the UNIQUE() function to get unique items from the list.
Get the SUM of distinct items
Once we have extracted unique items from the Product column, now we'll get the SUM of each item.
To get the sum, I am using two very popular Excel built-in functions called SUMIFS() and VLOOKUP().
Copy the below formula in "F2" column and press Ctrl+Shift+Enter keys.
=SUMIFS(B2:B10,A2:A10,VLOOKUP(E2:E8,A2:B10,1,FALSE))
Set focus on "F2" column and drag it down.
1) How to use VLOOKUP function in VBA
2) How to use VLOOKUP with IF statement in Excel
3) How to trap VLOOKUP #N/A errors with IFERROR in Excel
Now, whenever you make any changes in the 1st or 2nd columns, the changes will reflect in columns "E" and "F".
🙂