Use SUMIF if you need to sum values for a particular person or another criterion.
To sum cells by criteria, do the following:
1. Select the cell that will contain the result.
2. Do one of the following:
- On
the Formula tab, in the Function Library group, select the Math & Trig button:
Choose SUMIF in the list.
- Click the Insert Function button in the left of the Formula bar:
In the Insert Function dialog box:
- select Math & Trig in the Or select a category drop-down list,
- select SUMIF in the Select a function list.
3. In the Function Arguments dialog box:
- The Range field determines the range of cells Excel will look to perform the count in. In this example, the cell range is B2:B21.
- The Criteria is a conditional statement that is similar to the conditional statement in the IF statement.
- The Sum_range field tells Excel which cells to add when the criteria are met for each cell in the range. In this example, the cell range is D2:D21.
4. Press OK.
Notes:
- You can enter this formula using the keyboard, for this example:
= SUMIF (B2:B21, "*Revay", D2:D21) - You can use the wildcard characters, question mark (?), the asterisk (*) in the criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character. For example:
- Microsoft Excel provides additional functions that can be used to analyze your data based on a condition or criteria:
- To count the number of occurrences of a string of text or a number within a range of cells, use the COUNTIF function (see How to count cells by criteria for more details).
- To have a formula return one of two values based on a condition, use the IF function.
- To analyze data in a list based on criteria, such as profit margins or product types, use the database and list management functions (DCOUNT, DCOUNTA, DSUM, etc.).
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 Excel
In 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 Excel
Kutools 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 Excel
Besides 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 Excel
Sometimes, 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:
(1) Select the column name that you will sum based on, and then click the Primary Key button;
(2) Select the column name that you will sum, and then click the Calculate > Sum.
(3) Click the Ok button.
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 Excel
Kutools 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 Excel
Kutools 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:
- Sum entire column except header
- Sum values between two dates range in Excel
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!