How to Use Summation Formulas in Microsoft Excel

Опубликовал Admin
19-10-2016, 08:45
6 357
0
Microsoft Excel recognizes a number of mathematical functions that can be used to manipulate the data you have entered into a spreadsheet. Whether you’re working with a few numbers or large data sets, summation functions are good place to become familiar with Excel function logic. The most common function for simple addition across cells is “=SUM()”, with the target cell range placed between the parentheses. But there are a number of other ways that the software can handle this calculation as well.

Using the SUM Function

  1. Use the SUM function to add two or more cells. Type an equals (=) sign, the SUM function, and the numbers you are adding surrounded by parenthesis(). For example: =SUM(your numbers here) , or =SUM(C4,C5,C6,C7). This formula will add together all numbers and cells within the parentheses.
  2. Use the SUM function to add a range of cells. If you provide a start and an end cell, separated by a colon (:), you can include large sections of the spreadsheet in your calculation. For example: '=SUM(C4:C7) tells Excel to add the value from C4, the value from C7, and everything in between.
    • You don't have to type out "C4:C7" - you can click and hold on cell C4, and drag your mouse down to highlight all the cells from C4 to C7 to automatically enter the formula values. Add the parenthesis at the end, and you're done. For large columns of numbers, this is a much faster method than clicking on each and every cell individually.
  3. Use the AutoSum Wizard. Alternately, if you are using Excel 2007 or later, you can have excel perform this functions automatically by selecting a cell next to the desired range and pressing “AutoSum > Sum”.
    • AutoSum is limited to contiguous cell ranges - meaning if you want to skip cells in your calculation it may not work correctly.
  4. Copy/paste data into other cells. Since the cell with the function holds both the sum and the function, you have to consider which information you want copied.
    • Copy a cell (“Edit > Copy”), then select another cell and go to “Edit > Paste > Paste Special”. Here you can select whether to paste the cell value (sum result) or formula into the destination cell.
  5. Reference sums in other functions. The value of your summation cell can be called in other functions in your spreadsheet. Rather than re-adding information or typing out the number value of your previous function, you can reference the cell in other calculations to automatically use the result.
    • For example, if you add all of column C and want to add the result to sum of column D, rather than typing it all out you can refer to the cell containing the summation of column C in your summation formula for column D.

Using the Plus (+) Sign

  1. Enter the formula into a spreadsheet cell. Select a cell and type an equals (=) sign, then alternate between clicking on the first number you need to add, then typing the plus (+) sign, then clicking on the second number you want to add, and so on. Each time you click on another number, Excel will insert the cell reference for you (C4, for example), which tells Excel which spreadsheet cell contains the number (for C4, it's the cell in column C, in row 4). Your finished formula should look something like this: =C4+C5+C6+C7.
    • If you know which cells you want to calculate, you can type them out at once instead of selecting them individually.
    • Excel functions will recognize mixed numbers and cell entries. That is, you could add 5000+C5+25.2+B7.
  2. Hit the Enter key. Excel will automatically add the numbers together for you.

Using the SUMIF Function

  1. Set up your data for a SUMIF function. Since SUMIF can interpret non-numeric data, your data tables will need to be set up a little differently from a basic + or SUM function. Create one column with number values and a second column with a conditional value, like “yes” and “no”. For example, a column with 4 rows with values 1-4 and a second column with alternating values of “yes” or “no”.
  2. Enter the function into a cell. Select a cell and enter “=SUMIF” then enclose the conditions in parentheses. First you must enter a range, then criteria, then a second range to summate. In this case, the criteria the yes/no condition, the range will be the cells containing those criteria, and the summation range is the target values. For example: =SUMIF(C1:C4, yes, B1:B4). This means the column C, which contains the yes/no condition, will add any values from column B where column C reads “yes”.
    • The cell range will vary depending upon your data table.

Using the SUMIFS Function

  1. Setup your data table. The setup for this data table is much like for SUMIF, but it can support multiple criteria differing ranges. Make a column with numeric values, a second column with a conditional value (e.g. yes/no), and a third column with another conditional value (e.g. date).
  2. Enter your SUMIFS function. Select a cell and enter “=SUMIFS()”. Within the parentheses enter the summation range, criteria ranges, and target criteria. Important to note, with SUMIFS the sum rage is the first value. For example, =SUMIFS(B1:B4, C1:C4, yes, D1:D4, “>1/1/2011”). This will calculate the sum of column B, as long as column C has the condition “yes” and column D reads a date later than 1/1/2011 (“>” and “<” are symbols used to denote greater than and less than).
    • Note that the ranges can be variable, which can be useful for large data tables.

Tips

  • There’s no reason to use complex functions for simple math; likewise no reason to use a simple functions when a more complex function will make life simpler. Take the easy road.
  • These summation functions also work in other free spreadsheet software, like Google Sheets.
Теги:
Information
Users of Guests are not allowed to comment this publication.