Excel SUM function
The Excel SUM function is among the most common used in the Excel. It therefore deserves its own tutorial that will show you how to unlock its potential. We will demonstrate how to use the Excel SUM function to add a large quantity of data, contained over several pages. We shall also demonstrate how to simplify and automate these sums as much as possible.
SUM in Excel
The Excel SUM function adds all the numbers you specify as arguments. We can add numbers, individual cells, or entire ranges of cells. We can even add functions, provided that their result is a number. This tutorial assumes that you have basic knowledge of Excel formulas.
The Excel SUM function is recorded into the formula bar in the following manner:
= SUM (argument1 , argument2...argument30)
An argument can be thought of as anything that we wish to add (cell, cell reference, etc.). A formula may contain a maximum of 30 arguments. Thanks to this feature, we can add practically anything in Excel. In the example, we will show how to add a number to the value of a cell.
Example:
Our boss asked us to prepare a pricelist of goods with a 10 CZK surcharge, applicable to card payments.
We solve the problem using the Excel SUM function (the problem can be solved using various methods). Prepare a table with pricelist according to the example below:
- We calculate the price with surcharge in column C.
- In cell C2, we write the formula =SUM(B2,10) and copy the result for the other rows as well:
The principle of the formula is simple. It adds 10 to the value of the cell in column B. Adding ranges using the SUM function is far more interesting. For easier understanding, we shall demonstrate such a sum in an illustrated example:
Our bass has asked us to add the quarterly revenues of all products.
We can calculate the sum in multiple steps, requiring a lot effort. We can perhaps add the quarterly revenues for individual products, and then add these quarterly revenues together. We are familiar with the SUM function, however, and are able to determine the sum using a single formula. Prepare the following table in Excel:
The principle lies in the ability to add an entire range of cells. In our example, we can write the formula in cell E2 perhaps using the following form:
=SUM(B2:D4)
The range that we wish to add begins with cell B2 (January sales of IPads) and ends with cell C4 (March sales of IBooks). The range begins at cell B2 and ends at cell D4. The beginning and the end of the range in the formula is divided by a colon (:). The entire range is outlined in colour when writing the formula:
At first glance, the formula syntax taking the argument =SUM(B2:D4) is wonderfully simple and easy to follow. This is exactly what we want to achieve when writing formulas. We only used a single argument when writing this formula. Thanks to this savings, we could theoretically add another 29 arguments into the formula that we could add together. More about this topic, however, in the following chapter.
Adding data over multiple sheets
So far, we have only used the SUM function in Excel to perform additions on a single sheet. Unfortunately, that does not suffice today. Firms today produce a great quantity of data that simply cannot fit on a single sheet. All of us have likely encountered this at work. We shall now demonstrate how to add values over multiple sheets using the SUM function in Excel. Only two rules need to be observed when writing the formula:
- When writing formulas, we apply the rules that we have learned in the Excel and formula tutorial.
- I only fill in the name of the page, divided by an exclamation mark (!) in the cell reference or range.
It is best understood on an illustrated example. Our spreadsheet will contain three sheets (Sheet1, Sheet2, and Sheet3). We would like to add that value from cell A1 from the sheet labelled Sheet1 and the value of cell B1 from the sheet labelled Sheet2 into the sheet labelled Sheet3. In this case, the formula syntax shall take the following arguments:
=SUM(Sheet1!A1,Sheet2!B1)
We used two arguments in the Excel SUM function. The Excel SUM function allows us to add a maximum of 30 arguments, so we could thus add the values of thirty pages (if necessary).
The name of the Sheet is predefined by Excel. Fortunately, we can modify it and make formula writing easier to follow. We rename a sheet using the following procedure:
- We select (turns blue) the old name of the sheet (sheet names can be found in the lower left corner of the display) by double-clicking on it.
- We overwrite the selected name with the new one and press the Enter key.
The Excel SUM function has one great advantage. It can add all of the numbers on a sheet. If it finds a cell with text, it ignores it and does not include it in the calculation. This makes it easier to simply the formula. We can demonstrate this on the following example:
A firm has three independent departments: A, B, and C. Each department has its own sheet in an Excel file, labelled under its name, in which it records its expenses. The boss asks us to monitor the expenses of all the departments.
This situation is relatively common in smaller firms, in which expensive accounting programs are unfeasible - it is far cheaper to share a single excel file. The problem may be solved using the Excel SUM function in several ways. We shall demonstrate one ineffective and a second effect way of using the SUM function in Excel.
We shall first label the individual sheets based on the name of the departments and add a fourth sheet labelled Total:
We shall prepare a table on sheet A:
On sheet B:
And finally, on sheet C:
We shall enter the formula on the sheet Total. From the knowledge we have gained so far, we should be able to add data over several sheets using the Excel SUM function. We shall use a total of three arguments in the formula:
=SUM(A!A1:A3,B!A1:A3,C!A1:A3)
- The first argument shall add the cell range A1 to A3 on the sheet labelled A.
- The same shall apply in the second argument for sheet B.
- Finally, the third argument is added on sheet C.
The problem, however, can be solved using the Excel SUM function far more elegantly. We only need to meet a single condition, which we shall consider when creating the table. We must place the data that we wish to add over the individual sheets in the same ranges. Our problem meets this condition. Notice that we enter data on each sheet in the cell range A1 to A3. In such an event, we can write the Excel SUM function in the following form:
=SUM(A:C!A1:A3)
As we can see in the formula, we have saved two arguments through an easy modification, even though we are adding data from three sheets. In this modification, the SUM function adds the cell range between cell A1 and A3 from sheets A through C.
Advanced use of the SUM function
While the problem from the previous chapter is resolved, it is still missing something to be perfect. It is missing automatic entry of new values from the sheets of individual departments. Manually rewriting the formula in the Excel SUM function, every time we want a current sum, is inconvenient. Such a procedure is additionally prone to error. We can forget about updating a formula, or write it incorrectly, and some of the data would fail to be added using the Excel SUM function. The solution is to automatically enter data from individual sheets into the Excel SUM function.
We can automate data entry using dynamically labelled ranges or column (or row) labelling. In our example though, we are unable to perform both procedures – Excel will not allow us to assign the same name to several ranges. We thus automate the SUM function in the following way:
=SUM(A:C!A:A)
We simplified the formula by ordering the Excel SUM function to add all the values that lie in column A from the sheet labelled A up to sheet labelled C. This formula will always provide us with an updated sum of sheets from individual departments, without exercising any effort. Try the formula, on purpose, by filling in new data into Sheets A to C.
If you cannot manage to add all the sheets in the spreadsheet, then you can apply the above method to add entire spreadsheets. I do not personally recommend this procedure, however. It is more reliable and effective to save the data in a database and to import the required data into Excel. To make it complete, we shall discuss how to add data from a different worksheet.
The most important aspect is to describe the full path to the sheet in the formula. This means specifying on which disk and in which folder it is located. The name of the sheet must obviously not be missing either:
=SUM('C:\Documents and Settings\Work\[name-workbook.xls]name.sheet'!name cell, other argument...)
© Fotis Fotopulos, 2009
Pictures © Fotis Fotopulos, 2009