How to sum unique values based on criteria in Excel?
For example, I have a range of data which contains Name and Order columns, now, to sum only unique values in Order column based on the Name column as following screenshot shown. How to solve this task quickly and easily In Excel?
To sum only unique values based on criteria in another column, please use the below formula:
1. Please copy or enter the following array formula into a blank cell:
Note: In the above formula, A2:A12 is the column data contains the condition value, D2 is the condition that you want to sum values based on, B2:B12 is the column contains the values that you want to sum.
2. Then, press Ctrl + Shift + Enter keys together to get the first total result, and then, select this formula cell and drag the fill handle down to the cells to get all total values for each item. See screenshot:
Tips: If you need to sum all the unique values based on two criteria, please apply the below formula:
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 January 2016 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 number 40+70=110. How to achieve it? Method in this article can help you.