Hello everyone! I need some assistance with a Google Sheets formula. I’m tracking some income data and would like to get the unique product names along with the total count of each. For instance, if “Product A” appears twice with counts of 3 and 1, the output should show “Product A - 4”. I’m looking for an automated formula to calculate this for me. Any help would be appreciated!
To achieve your goal, you can use a mix of the UNIQUE and SUMIF functions in Google Sheets. Start by applying UNIQUE to retrieve distinct product names from your specified range, like =UNIQUE(A:A). Next, use SUMIF to sum the quantities for each unique product by referencing the relevant columns, such as =SUMIF(A:A,D2,B:B). Finally, concatenate the results to display them in the format you need, for example, =D2&" - "&E2. This method proved efficient for managing my own inventory data. Just be cautious about having blank cells, as they can affect how UNIQUE works.
try using =QUERY(A:B,“select A, sum(B) group by A”) if your data is in columns A and B. this will give you the product names with totaled counts automatically without needing multiple formulas. way simpler than the unique/sumif approach imo
Another approach that worked well for me is using a pivot table which might be easier if you’re not comfortable with formulas. Go to Insert > Pivot table, then add your product names as rows and set the count values as values with SUM aggregation. While this doesn’t give you the exact “Product A - 4” format automatically, you can reference the pivot table results in another column using something like =A2&" - "&B2 to get your desired output. The advantage is that pivot tables update automatically when you add new data, and you can easily sort by highest counts or alphabetically. I found this method particularly useful when dealing with larger datasets where formula-based solutions started slowing down the spreadsheet.