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).

sum values based another column1

#2 keep the pasted values in Column D selected, go to DATA tab, click Remove Duplicates command under Data Tools group.

sum values based another column2

#3 select only pasted column in columns list under Remove Duplicates dialog box. And click Ok button.

sum values based another column5

Note: the duplicated values in selected Column D have been removed and only keep unique values.

sum values based another column6

#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)

sum values based another column7

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.

sum values based another column8

#2 go to INSERT tab, click PivotTable command under Tables group. The Create PivotTable dialog box will open.

sum values based another column9

#3 select the Existing Worksheet radio button, and select one range of cells that used to place the pivot table.

sum values based another column10

#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.

sum values based another column11

#5 the values in column B based on another column A have been summed.

sum values based another column12

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
How to Sum if Equal to Many Items 12

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
How to Sum if Equal to X or Y 19

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
How to Sum in Last N Days 14

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
How to Sum if Contains an Asterisk 7

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
How to Sum in Vertical Range 8

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
How to Sum in Horizontal Range9

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
How to Sum with Criteria and Or Logic in Excel 8

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
How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column14

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
How to Sum by Formula If Cells Are Not Blank in Criteria13

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
How to Sum by Formula if Cell Ends with in Excel14

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