# Sum Values in a Column based on Criteria in Another Column

This post will guide you how to sum values in a column based on criteria in another column in Excel. How do I sum values based on criteria in another column with Pivot table in Excel. How to sum all values in one column based on the text value in another column with formula in Excel.

Assuming that you have a list of data set in range A1:B6 and you want to sum the values in one column (Column B) based on the text value in another column (Column A), how to achieve it. You need to remove duplicates values in Column a, then sum values in column B based on the unique text values in column A. You can use the below two methods to achieve the result.

## Sum Values based on Another Column with Formula

To use a formula to sum values in Column B based on Column A, you can create a formula based on the SUMIF function. Just do the following steps:

#1 select the text values in Column A (A1:A6), press Ctrl +C to copy these values, and paste into another blank column (Column D). #2 keep the pasted values in Column D selected, go to DATA tab, click Remove Duplicates command under Data Tools group. #3 select only pasted column in columns list under Remove Duplicates dialog box. And click Ok button. Note: the duplicated values in selected Column D have been removed and only keep unique values. #4 select one blank cell (E2) beside the pasted column and then type the following formula into the formula box and press Enter key in your keyboard, and drag the AutoFill Handle over other cells.

`=SUMIF(\$A\$2:\$A\$6, D2, \$B\$2:\$B\$6)` You will see that the summed values have been calculated in column E based on the text values in Column A.

## Sum Values based on Another Column with Pivot Table

You can also create an Excel Pivot Table to sum values based on another column. Just do the following steps:

#1 select the data source that to be used for creating PivotTable. #2 go to INSERT tab, click PivotTable command under Tables group. The Create PivotTable dialog box will open. #3 select the Existing Worksheet radio button, and select one range of cells that used to place the pivot table. #4 Choose fields to add to report in the PivotTable Fields pane. Drag the column contain text values into the ROWS section, and drag the column contain sale values into the VALUES section. #5 the values in column B based on another column A have been summed. ### Related Functions

• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…
Related Posts
How to Sum if Equal to Many Items or A Range in Excel

If we want to sum numbers from a range with criteria, we often select SUMIF of SUMIFS functions as the first choice to create a formula. The criteria can be a number or an array or a collection of some ...

How to Sum if Equal to X or Y in Excel

In daily work, if we want to sum numbers from a range, and only sum the numbers which being equal to X or Y in the range, we can create a formula with Excel build-in functions to get the result. ...

How to Sum if Contains an Asterisk

In our daily life, we may want to sum amounts or sales for a specific period, for example in last N days. Sum numbers in Excel is easy to run, we can apply SUM function. But if we want to ...

How to Sum if Contains an Asterisk

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If ...

How to Sum in Vertical Range

If we want to add numbers based on some conditions in Excel worksheet, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In this article, we will introduce you the method ...

How to Sum in Horizontal Range

To add numbers together we need to apply SUM function. But if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In ...

How to Sum with Criteria and Or Logic in Excel

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If ...

How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column

Sometimes we may meet the case that subtotal values for different groups and only record a subtotal value for one group in a column, for other cells in this group, keep them blank. Today we will introduce you how can ...

How to Sum by Formula If Cells Are Not Blank in Criteria Range

Sometimes we may meet the cases that some blank cells exist in criteria range or sum range. In most situations we will ignore them, so we need to filter data by ‘not blank/not empty’. Today we will introduce you how ...

How to Sum by Formula if Cell Ends with in Excel

We have introduced the method of sum numbers based on criteria “sum if cell begins with” (you can refer to ‘How to Sum Data if Begins with in Excel’ on our website). In this article, we will show you the ...

Sidebar