How to sum unique values based on criteria in Excel?
When working with Excel datasets—such as order logs, financial records, or survey results—you may often encounter the need to calculate the sum of unique values from one column based on filters or criteria in another column. For instance, consider a data table containing two columns: Name and Order. If you want to calculate the sum of only the unique Order values for each Name (ignoring any repeated values), how can you achieve this efficiently in Excel? This is a common requirement in many business or data analysis scenarios where simply summing all matching items would yield inflated results due to duplicates.
The sample screenshot below demonstrates a typical scenario: given a list of names and their corresponding order values—including duplicates—you want to summarize the data by summing unique order values for each name separately.
Common challenges in this task include identifying unique entries based on specific criteria, ensuring that only the first occurrence is counted, and preventing manual errors that may arise from copying and pasting filtered data. Several practical Excel approaches, including array formulas, Kutools, and Power Query can help you solve this problem, each suited to different usage scenarios.
- Sum unique values based on one or more criteria with array formulas
- Sum Unique Values Based on Criteria Using Kutools for Excel’s Advanced Combine Rows
- Other Built-in Excel Methods: Use Pivot Table for unique sum analysis
<h4"> Sum unique values based on one or more criteria with array formulas
One effective and flexible approach is to use array formulas, which allow you to summarize unique values that meet specific criteria. This works especially well when you want the calculation to update automatically as the data or criteria change.
To sum only the unique values in a column according to the filter or condition in another column, you can apply the following formula:
1. In a blank cell (for example, E2), enter this formula:
=SUM(IF(FREQUENCY(IF($A$2:$A$12=D2,MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW($B$2:$B$12)-ROW($B$2)+1),$B$2:$B$12))Before confirming the formula, carefully check that:
- A2:A12: The range containing the criteria (names in this case).
- D2: The cell where your target condition (such as a specific name) is located.
- B2:B12: The range of values you want to sum uniquely.
You can adjust these ranges as needed based on your actual data structure. Ensure all ranges are equal in length to avoid formula errors.
2. To activate this array formula, press Ctrl + Shift + Enter at the same time after typing it in. Curly braces will appear around the formula, indicating it's an array formula. Then, drag the fill handle down to copy the formula for each corresponding value in your summary column. This way, each item gets the correct unique sum automatically.
Practical tip: If you are using Excel 365 or Exce l2021, new dynamic array functions like UNIQUE and SUMIFS might further simplify some of these calculations, but the above formula works reliably across many Excel versions.
=SUMIF(A2:B12, UNIQUE(D2), B2:B12)
Deal with more criteria: =SUM(SUMIFS(sum_range, criteria_range1, UNIQUE(criteria_range1), [criteria_range2, criteria2], ...)
Precautions:
- Ensure you are using array formula entry (Ctrl + Shift + Enter) if you’re on Excel 2019 or earlier. For Excel 365/2021, a regular Enter may suffice for dynamic formulas.
- If your data ranges are especially large, the array approach may become slower, so consider filtering your data first or using other methods for very large datasets.
- Watch carefully for extra spaces or data type consistency—uneven text or number formatting may cause non-matching errors.
Tips: If you need to sum all the unique values based on two criteria, the following extension of the array formula can be used:
=SUM(IF(FREQUENCY(IF($A$2:$A$12=E2,IF($B$2:$B$12=F2,MATCH($C$2:$C$12,$C$2:$C$12,0))),ROW($C$2:$C$12)-ROW($C$2)+1),$C$2:$C$12))This formula is similar in principle, but supports an additional filter from the B column (now compared with F2 as a secondary condition), and sums unique values from column C. After entering this formula into your selected summary cell, use Ctrl + Shift + Enter to confirm it, and then apply it to other summary rows as needed.
Summary suggestion: While array formulas deliver accurate results for most scenarios, always double-check for hidden duplicates (such as those with extra spaces or text formatting differences) and ensure your summary areas pull from accurately filtered lists.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Sum Unique Values Based on Criteria Using Kutools for Excel’s Advanced Combine Rows
Kutools for Excel’s Advanced Combine Rows feature makes sum only unique values based on a specific condition effortless! With just a few clicks, it intelligently groups your data and applies custom summary logic—no formulas, no hassle, just accurate results.
Step 1: Select Your Data Table
Highlight the full table including the headers.
Step 2: Go to Kutools > Content > Advanced Combine Rows.
Step 3: Set the Grouping Column
In the pop-up dialog, choose the column you want to group by(e.g., Fruit), set it to Primary Key in the Operation section.
Step 4: Set the Field for Unique Sum
Select the Sales column, set the calculation you want (e.g.,Sum) in the Operation section.
Tip: You can preview the combined result in the dialog.
Step 5: Click OK The table is now grouped by customer, each with the sum of unique product amounts.
Other Built-in Excel Methods: Use Pivot Table for unique sum analysis
Excel's PivotTable feature provides another powerful built-in approach to summarizing data based on criteria. While PivotTables do not directly sum unique values by default, post-Excel2013, they support the Distinct Count calculation, which helps you analyze the unique entry count for a specified field. Although this doesn’t calculate the unique value sum directly, you can use Distinct Count together with manual adjustment or a calculated field to facilitate a similar summary.
Advantages: PivotTables require no formula memorization or VBA coding, and they offer highly flexible drag-and-drop interfaces. Suitable for periodic reporting, group analysis, quick overviews, or when collaborating across teams. However, they are best for summary and analysis, not for constructing formulas for further calculation or automation.
Here’s how to use a PivotTable for unique sum analysis:
- Select your data range (for example, A1:B12 including headers), and go to Insert > PivotTable. In the dialog, select whether to place the PivotTable in a new worksheet or an existing one.
- In the PivotTable Field List, drag Name to the Rows area, and Order to the Values area.
- For the Order entries in the Values area, click the drop-down arrow > Value Field Settings > set to Sum (shows total order sum including duplicates).
Limitations:
- The Distinct Count function is only available in Excel2013 or newer; earlier versions require more manual work.
In summary, while PivotTable is excellent for interactive summary and analysis, consider pairing it with formula or VBA-based approaches if you need true unique sum calculations.
More relative articles:
- Sum Multiple Columns Based On Single Criteria In Excel
- In Excel, you may always need to sum multiple columns based on one criteria. For example, I have a range of data as following screenshot shown, now, I want to get the total values of KTE in three months - Jan, Feb and Mar.
- Vlookup And Sum Matches In Rows Or Columns In Excel
- Using vlookup and sum function helps you quickly find out the specified criteria and sum the corresponding values at the same time. In this article, we are going to show you two methods to vlookup and sum the first or all matched values in rows or columns in Excel.
- Sum Values Based On Month And Year In Excel
- If you have a range of data, column A contains some dates and column B has the number of orders, now, you need to sum the numbers based on month and year from another column. In this case, I want to calculate the total orders of January2016 to get the following result. And this article, I will talk about some tricks to solve this job in Excel.
- Sum Values Based On Text Criteria In Excel
- In Excel, have you ever tried to sum values based on another column of text criteria? For example, I have a range of data in worksheet as following screenshot shown, now, I want to add up all the numbers in column B corresponding with the text values in column A that meet a certain criterion, such as sum the numbers if the cells in column A contains KTE.
- Sum Values Based On Selection Of Drop-Down List In Excel
- As below screenshot shown, you have a table contains a Category column and an Amount column, and you have created a data validation drop-down list which contains all the categories. When selecting any kind of category from the drop-down list, you want to sum up all corresponding cell values in Column B and populate the result in a specified cell. For example, when selecting category CC from the drop-down list, you need to sum values in cell B5 and B8 and get the total number40+70=110. How to achieve it? Method in this article can help you.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in