Microsoft Excel can automatically recognize a number of mathematical functions that can be used to manipulate data as it is entered into a cell. Regardless of whether you are working on a few numbers or a large set of data, the functions that perform the sum of multiple values are best suited to get started with the vast set of formulas that Excel offers. The simplest formula for summing multiple values and displaying the result within a given cell is the "= SUM ()" function. The range of cells containing the values to be added must be inserted inside the brackets of the formula. However, there are a wide variety of ways that Excel can be used to add up two or more numbers.
Choose a Method
- SUM function- useful within very large worksheets, as it can add up to large ranges of cells. As input parameters it accepts only numbers and not conditional values.
- Mathematical Operator +: it is very simple and intuitive, but it is not efficient. It is useful in the case of sums that require the use of few values.
- SUMIF function: allows you to specify a condition and the summation will be performed only if this condition is satisfied.
-
SUMIFS function: allows you to add certain values only if the specific conditions entered are satisfied. It is not available in Excel 2003 and in earlier versions.
Steps
Method 1 of 4: SUM function
Use Summation Formulas in Microsoft Excel Step 1 Step 1. Use the SUM function to sum the values contained in two or more cells
Start by typing the equal sign ("="), enter the keyword "SUM", then enter the range of cells or values to add by placing them in parentheses. For instance, = SUM (values_to_sum) or = SUM (C4, C5, C6, C7). This formula sums all cells or all values listed in parentheses.
Use Summation Formulas in Microsoft Excel Step 2 Step 2. Use the SUM function to add up the values stored in a range of cells
If you enter a starting cell and an ending cell separated by a colon (":") in the formula, you will be able to add up a large number of cells in the worksheet. For example the formula = SUM (C4: C7) tells the program to add the values contained in cells C4, C5, C6 and C7.
To indicate the range of cells to be added, it is not necessary to enter the value "C4: C7" in the formula, simply click on cell "C4" and drag the mouse cursor down to cell "C7". The selected cell range will be automatically inserted into the formula. At this point, you will simply need to add the final closing parenthesis to complete the function syntax. For a wide range of values, this method is much faster than clicking individually on each cell to include in the summation
Use Summation Formulas in Microsoft Excel Step 3 Step 3. Use Excel's "AutoSum" feature
If you are using Excel 2007 or a later version, you have an alternative which is to let the program perform the sum automatically by simply selecting the values to add and clicking on the "Auto Add" button.
The "AutoSum" feature can only be used to add up values stored in contiguous cells. This means that if you need to exclude certain cells in the range in question from the sum, the final result will not be correct
Use Summation Formulas in Microsoft Excel Step 4 Step 4. Copy and paste the data into other cells
Since the cell where you entered the function contains both the formula and the result, you will need to indicate what information you want to copy.
Copy a cell (click on the "Edit" menu and choose the "Copy" option), then select the destination cell, click on the "Edit" menu, click on the "Paste" item and finally choose the "Paste Special" option. At this point, you can choose whether to paste the copied value (i.e. the result of the summation) or the formula into the cell
Use Summation Formulas in Microsoft Excel Step 5 Step 5. Refer to a summation within other functions
The result of the summation you calculated can be used within other formulas in the spreadsheet. Instead of doing the sum again or manually typing the result of the formula you have already used, you can directly refer to the cell that contains it in order to use the corresponding value within other formulas.
For example, if you have already performed the sum of all the values in column "C" of the sheet and you want to add the result obtained to the sum of the cells in column "D", you can do so by referring to the corresponding cell, rather than re-entering all the values within the formula you use to calculate the sum of the cells in column "D"
Method 2 of 4: Mathematical Operator +
Use Summation Formulas in Microsoft Excel Step 6 Step 1. Enter the formula into the spreadsheet
Select a cell and type in it the equal sign ("="), click on the first value to add, type the "+" sign, click on the second value of the formula, then repeat the action for all the numbers you want include in the summation. Each time you click on the number to include in the summation, Excel will insert the reference to the corresponding cell into the formula (for example "C4"). In this way, the program will know where the number that will be included in the summation is stored (in the example it is cell number 4 of column C). When you are done inserting, the final formula will look like this: = C4 + C5 + C6 + C7.
- If you already know the cells you want to add to the summation, you can create the formula by typing it manually, without having to click on each individual cell.
- Excel is able to work with mixed formulas where there are numbers and cell references. For example, you can easily perform the following calculation: 5,000 + C5 + 25, 2 + B7.
Use Summation Formulas in Microsoft Excel Step 7 Step 2. Press the "Enter" key
Excel will automatically perform the summation indicated in the formula.
Method 3 of 4: SUMIF function
Use Summation Formulas in Microsoft Excel Step 8 Step 1. Set the data for the "SUMIF" formula
Since the "SUMIF" function accepts non-numeric values as input, the table of the data to be analyzed must be configured in a slightly different way from that used for the "SUM" function or for formulas that simply use the mathematical operator " + ". Start by creating a column in which the numeric values to be added must be entered, then add a second column in which the conditional values to be tested, such as "yes" and "no", must be entered. For example, create a table consisting of four rows and two columns. In the first column enter the values from 1 to 4, while in the second column alternate the values "yes" and "no".
Use Summation Formulas in Microsoft Excel Step 9 Step 2. Enter the formula inside a specific cell
Select the latter and type the command "= SUMIF", then insert the conditions inside the round brackets. As the first parameter you must enter a range of values to be tested, then the conditional criteria followed by the range of values to be added. In the example, the criteria to be considered (the values "yes" and "no") represent the first range, while the numbers to be added represent the second range. For example, the formula "= SUMIF (C1: C4, yes, B1: B4)" indicates that the conditional values to be tested are stored in column C, while the numbers to be added are present in column B. The result will be given by the sum of all the numbers relating to the cells of column C that contain the "yes" parameter.
The cell ranges to consider vary based on the structure of the worksheet
Method 4 of 4: SUMIFS function
Use Summation Formulas in Microsoft Excel Step 10 Step 1. Set up the data table to analyze
The data structure is similar to that used for the "SUMIF" formula, but in this case you can set multiple conditions. Create a column in which to insert the numeric data, a second column in which there are the data to be tested (for example "yes" and "no") and a third column with other conditional values (for example dates).
Use Summation Formulas in Microsoft Excel Step 11 Step 2. Enter the "SUMIFS" function
Select a cell and type the following command "= SUMIFS ()". The range of data to be added and the ranges of criteria to be evaluated must be entered inside the round brackets. Important note: in the case of the "SUMIFS" formula, the first range must be that of the numeric values to be added. For example, the formula "= SUMIFS (B1: B4, C1: C4, yes, D1: D4,"> 1/1/2021 ")" adds up all the values in column "B" that have the criterion "yes" in the corresponding cell of column C and that have a date greater than "1/1/2021" in the corresponding cell of column D (the ">" operator is used to perform a comparison and understand if a number or date are greater than a given value or other element of the same nature).
Note that a variable number of criteria can be analyzed, so this formula is suitable for analyzing large data sets
Advice
- There is no reason to use complex functions to perform simple mathematical operations, just as there is no reason to solve complex problems by creating functions from scratch when there is a suitable formula that can make things much easier. Always try to choose the most advantageous option.
- The functions described in this article also work with other Excel-like programs, such as Google Sheets.
