- HOW TO SUM A COLUMN IN EXCEL BASED ON ANOTHER COLUMN FULL
- HOW TO SUM A COLUMN IN EXCEL BASED ON ANOTHER COLUMN PLUS
HOW TO SUM A COLUMN IN EXCEL BASED ON ANOTHER COLUMN FULL
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īesides using formula, you also can sum the values based on criteria in another column by inserting a Pivot table.ġ. Kutools for Excel- Includes more than 300 handy tools for 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. Change values and formulas, or add your own values and formulas and watch the results change, live.Ĭopy all the cells in the table below, and paste into cell A1 in a new worksheet in Excel.Easily sum/count/average values based on criteria in another column in Excel You can work with sample data and formulas right here, in this Excel for the web workbook. If you want to experiment with the SUMIFS function, here’s some sample data and a formula that uses the function. If you click SUMIFS in Formula AutoComplete, an article opens to give you more help. Looking at the image of Formula AutoComplete and the list of arguments, in our example sum_rangeis D2:D11, the column of numbers you want to sum criteria_range1is A2.A11, the column of data where criteria1 “South” resides.Īs you type, the rest of the arguments will appear in Formula AutoComplete (not shown here) criteria_range2 is C2:C11, the column of data where criteria2 “Meat” resides.
After you type =SUMIFS(, Formula AutoComplete appears beneath the formula, with the list of arguments in their proper order. The result, again, is 14,719.Īs you type the SUMIFS function in Excel, if you don’t remember the arguments, help is ready at hand. End the formula with a closing parenthesis ) and then press Enter.
HOW TO SUM A COLUMN IN EXCEL BASED ON ANOTHER COLUMN PLUS
Quotation marks around “South” specify that this text data.įinally, you enter the arguments for your second condition – the range of cells (C2:C11) that contains the word “meat,” plus the word itself (surrounded by quotes) so that Excel can match it. Notice the commas between the separate arguments: Next, you want to find data that meets two conditions, so you enter your first condition by specifying for the function the location of the data (A2:A11) and also what the condition is-which is “South”. That cell range is the first argument in this formula-the first piece of data that the function requires as input. In other words, you want the formula to sum numbers in that column if they meet the conditions. The first step is to specify the location of the numbers: It calculates numbers, which in this case are in column D.
Let's look more closely at each part of the formula.
Here’s a formula you can use to acomplish this: Have a look at this example in which we have two conditions: we want the sum of Meat sales (from column C) in the South region (from column A).
This is a good case for using the SUMIFS function in a formula. Let's say that you need to sum values with more than one condition, such as the sum of product sales in a specific region. Excel for Microsoft 365 Excel for the web Excel 2021 Excel 2019 Excel 2016 Excel 2013 More.