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.

Table of Contents

**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])…

## Leave a Reply

You must be logged in to post a comment.