# How to Sum Values Based on Criteria List in Another Column in Excel?

Suppose we have two lists in excel, one list records the product serial numbers, these serial numbers can be duplicate, and the other list records the sales volume for products accordingly, for more details you can see the screenshot in precondition below. To calculate the total sales volume, normally we can use SUM function in excel. But if we only want to sum the sales volume for only one product, how can we do? In a traditional way, we can do filter firstly to filter out the product we want, then calculate the total values one by one. But this way is boring and time-consuming especially the data amount is large. Above all, we need to find another simple way to sum values for specified product to meet our requirement. This free tutorial will introduce you a convenient way to sum total values based on criteria, you cannot miss it.

Precondition:

See screenshot below, we prepare two lists, the first column records the product serial number, the second one records the income for all products. We can see that some products are duplicate in the list, if we want to calculate the total income for these products, in a traditional way we have to sum all of them one by one. ## Method 1: Sum Values Based on Criteria in Another Column

Step 1: Copy ‘Product’ column to another column first. Step 2: On column D, click Data in ribbon, then click Remove Duplicate under Data Tools group. Step 3:Remove Duplicates’ dialog pops up. Just click OK. The you will notice that duplicate values are removed from column D. All products are unique. Step 4: Select a blank cell next to the filtered product list, for example E2, enter the following formula =SUMIF(\$A\$2:\$A\$19, D2, \$B\$2:\$B\$19). Step 5: Click Enter to get result. Verify that total income is calculated properly. Step 6: Drag the fill handle down till the end of list. Verify that total income is calculated properly for each product. ## Method 2: Sum Values Based on Criteria by PivotTable

We can also calculate total income for all products via excel PivotTable.

Step 1: Select the range A1:B19, then click Insert in ribbon, click PivotTable to create a PivotTable for the selected range. Step 2: After above operating, ‘Create PivotTable’ dialog pops up. Choose the location to save PivotTable. We choose existing workbook and started cell G1 to save it. Then click OK. Step 3: On PivotTable Fields screen, check on Product and Income, move Product under Rows, under Values select ‘Sum of Income’. Step 4: Verify that PivotTable for the selected range is inserted properly. You can select the small arrow button to select product to see the sum of income. ### 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])…
• Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
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