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.

How to Sum Values Based on Criteria List in Another Column in Excel1

Method 1: Sum Values Based on Criteria in Another Column


Step 1: Copy ‘Product’ column to another column first.

How to Sum Values Based on Criteria List in Another Column in Excel2

Step 2: On column D, click Data in ribbon, then click Remove Duplicate under Data Tools group.

How to Sum Values Based on Criteria List in Another Column in Excel3

Step 3:Remove Duplicates’ dialog pops up. Just click OK.

How to Sum Values Based on Criteria List in Another Column in Excel4

The you will notice that duplicate values are removed from column D. All products are unique.

How to Sum Values Based on Criteria List in Another Column in Excel5

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

How to Sum Values Based on Criteria List in Another Column in Excel6

Step 5: Click Enter to get result. Verify that total income is calculated properly.

How to Sum Values Based on Criteria List in Another Column in Excel7

Step 6: Drag the fill handle down till the end of list. Verify that total income is calculated properly for each product.

How to Sum Values Based on Criteria List in Another Column in Excel8

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.

How to Sum Values Based on Criteria List in Another Column in Excel9

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.

How to Sum Values Based on Criteria List in Another Column in Excel10

Step 3: On PivotTable Fields screen, check on Product and Income, move Product under Rows, under Values select ‘Sum of Income’.

How to Sum Values Based on Criteria List in Another Column in Excel11

Step 4: Verify that PivotTable for the selected range is inserted properly.

How to Sum Values Based on Criteria List in Another Column in Excel12

You can select the small arrow button to select product to see the sum of income.

How to Sum Values Based on Criteria List in Another Column in Excel13

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

Sidebar