Guest Author - Chris Curtis
There are eight ways to add a set of numbers in Excel. Each method has a specific use and produces explicit results. Below is an explanation of each methodology including its usage and expected results.
1. Add Numbers
You can type numbers directly into a cell (=2 + 2 will display 4 in the cell) Keep in mind this methodology is not dynamic.
2. Add Numbers in Adjoining Cell Locations
Activate the cell below the row of numbers to be added where you want the summation to be stored. Use the AutoSum Button (looks like a Greek E) located on the Standard tool bar to sum the numbers above. Press ENTER if the correct range is displayed. Adjust the range by selecting the correct range with your mouse, if it is not selecting the correct cells you want to sum. Use this same technique to sum numbers in a row, by activating a cell to the right of the range of numbers to be summed.
3. Add Numbers Located in Non-Adjoining Cell Locations
You can use the Sum function to add numbers that are not in adjacent cells. The Sum function allows you to include up to 30 non-adjoining cells or range references.
4. Add Numbers Based on One Condition
The SumIf function builds in the power to extract a sum based on a single criteria you define from a range of numbers. It takes three arguments: Range to evaluate, Criteria, Range to sum) If the criteria is met in the range to evaluate the value in the range to sum is included in the summation.
5. Add Numbers Based on Multiple Conditions
Nesting the Sum and IF functions in an array formula empowers you to extract a sum based on more than one criteria you define from a range of numbers. When entering in an array formula, you must press CTRL + Shift + ENTER)
Below is the syntax for creating a multiple criteria that is stored in separate ranges. This sets up an "and condition" where both conditions must be met to be included in the sum.
=Sum(IF((1st Range to be evaluated="1st Criteria")*(2nd Range to be evaluated="2nd Criteria"),Range to be Summed))
Below is the syntax for evaluating multiple criteria in the same range. This sets up an "Or Condition" where the value will be included if either criteria is met.
=Sum(If((Range to be evaluated="1st Criteria")+(Range to be evaluated="2nd criteria"),Range to be summed))
6. Add Numbers Based on Information in a Criteria Range
The DSUM provides you with the flexibility to extract sums based on either an "And Condition" or an "Or Condition" by using a separate criteria range. The criteria range mirrors the column labels in your data set and you set the criteria by identifying it below the column label in the criteria. If you are looking for an Or condition, you would identify each criteria on a separate row in single column, whereas and And condition, you would identify each criteria on the same row in the correct column. The syntax is as follows:
=DSUM(Range to Evaluate, Field Label of the column to sum, Criteria Range)
7. Add Numbers Based on Multiple Conditions using the Conditional Sum Wizard
You can use the Conditional Sum Wizard to summarize values in a list based on specified conditions. The Conditional Sum Wizard Add-In must be loaded for you to use this feature. Be aware that the formulas created by the Conditional Sum Wizard are array formulas and you must press CTRL + Shift + ENTER after editing the formula to maintain the correct formula syntax.
There are 4 steps to the Conditional Sum Wizard
Step 1: identifies the range of cells where the list containing the values to be added are located. If the lsit conforms to Excel's requirements for a data list, the proper range will be selected. It is a good idea, however, to verify that it has made the correct range selection.
Step 2: sets up the conditions to be met and the column label from which to extract the sum.
Step 3: give you the choice of copying the conditional values and the formula in your worksheet or to copy only the formula
Step 4: establishes the cell(s) in which to display the results.
8. Quick Sum on Status Bar
Imbedded in the status bar is a feature that you can use to obtain a quick sum on a range of numbers that you select. You can select multiple non-adjoining ranges for a cumulative total. As soon as the range of numbers has been selected the Sum will be displayed on Excel's status bar. This is not transferable to a worksheet cell but rather designed to give you a quick snap shot of the total.
