Use SUMIF if you need to sum values for a particular person or another criterion. Show To sum cells by criteria, do the following: 1. Select the cell that will contain the result. 2. Do one of the following:
3. In the Function Arguments dialog box:
4. Press OK. Notes:
See also this tip in French: Comment calculer la somme des cellules par critères. Sometimes you want to sum the values based on criteria in another column, for instance, here I only want to sum up the "Sale Volume" column where the corresponding "Product" column equals "A" as show as below, how can you do it? Of course, you can sum them one by one, but here I introduce some simple methods for you to sum the values in Excel. Sum values based on criteria in another column with formula in Excel Sum values based on criteria in another column with Pivot table in Excel Sum values based on criteria in another column with Kutools for Excel Split data to new sheets by criteria column, and then sum Sum values based on criteria in another column with formula in ExcelIn Excel, you can use formulas to quickly sum the values based on certain criteria in an adjacent column. 1. Copy the column you will sum based on, and then pasted into another column. In our case, we copy the Fruit column and paste in Column E. See screenshot left. 2. Keep
the pasted column selected, click Data > Remove Duplicates. And in the popping up Remove Duplicates dialog box, please only check the pasted column, and click the OK button. 3. Now only unique values are remained in the pasted column. Select a blank cell besides the pasted column, type the formula =SUMIF($A$2:$A$24, D2, $B$2:$B$24) into it, and then drag its AutoFill Handle down the range as you need. And then we have summed based on the specified column. See screenshot: Note: In above formula , A2:A24 is the column whose values you will sum based on, D2 is one value in the pasted column, and B2:B24 is the column you will sum. Easily sum/count/average values based on criteria in another column in ExcelKutools for Excel’s Advanced Combine Rows utility can help Excel users to batch sum, count, average, max, min the values in one column
based on the criteria in another column easily. Full Feature Free Trial 30-day! Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now Sum values based on criteria in another column with Pivot table in ExcelBesides using formula, you also can sum the values based on criteria in another column by inserting a Pivot table. 1. Select the range you need, and click Insert > PivotTable or Insert > PivotTable > PivotTable to open the Create PivotTable dialog box. 2. In the Create PivotTable dialog box, specify the destination rang you will place the new PivotTable at, and click the OK button. 3. Then in the PivotTable Fields pane, drag the criteria column name to the Rows section, drag the column you will sum and move to the Values section. See screenshot: Then you can see the above pivot table , it has summed the Amount column based on each item in the criteria column. See screenshot above: Sum values and combine based on criteria in another column with Kutools for ExcelSometimes, you may need to sum values based on criteria in another column, and then replace original data with the sum values directly. You can apply Kutools for Excel's Advanced Combine Rows utility. Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Free Trial Now! 1. Select the range that you will sum values based on criteria in another column, and click Kutools > Content > Advanced Combine Rows. Please note that the range should contain both the column you will sum based on and the column you will sum. 2. In the opening Combine Rows Based on Column
dialog box, you need to: Now you will see the values in the specified column are summed based on the criteria in the other column. See screenshot above: Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now Demo: Sum values based on criteria in another column with Kutools for ExcelKutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now! Easily split a range to multiple sheets based on criteria in a column in ExcelKutools for Excel’s Split Data utility can help Excel users easily split a range to multiple worksheets based on criteria in one column of original range. Full Feature Free Trial
30-day! Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now Relative Articles:
The Best Office Productivity ToolsKutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
How do you sum values based on conditions?For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John." To sum cells based on multiple criteria, see SUMIFS function.
How do you sum data with multiple criteria?If you need to sum numbers based on multiple criteria, you can use the SUMIFS function. The first range (D4:D11) are the cells to sum, called the "sum range". Criteria are supplied in pairs... (range / criteria).
How do I sum values based on criteria in the same column in Excel?2. To sum with more criteria, you just need to add the criteria into the braces, such as =SUM(SUMIF(A2:A10, {"KTE","KTO","KTW","Office Tab"}, B2:B10)). 3. This formula only can use when the range cells that you want to apply the criteria against in a same column.
|