Let's say you want to see the number of products in an inventory list grow as you add products each day. Use a running total (also called a running count) to watch the number of items in a worksheet add up as you enter new items. To calculate a running total, use the following procedure.
- Set up a worksheet like the following example.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
| |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
|
| A |
B |
C |
D |
E |
| Date |
Product |
Quantity |
Formula |
Running total |
| 2-Jan |
Beach toys |
5 |
=SUM($C$2:$C2) |
5 |
| 2-Jan |
Sunglasses |
3 |
=SUM($C$2:$C3) |
8 |
| 3-Jan |
Beach toys |
9 |
=SUM($C$2:$C4) |
17 |
| 3-Jan |
Sunglasses |
8 |
=SUM($C$2:$C5) |
25 |
| 3-Jan |
Swing set |
1 |
=SUM($C$2:$C6) |
26 |
| 4-Jan |
Beach toys |
6 |
=SUM($C$2:$C7) |
32 |
| 4-Jan |
Sunglasses |
4 |
=SUM($C$2:$C8) |
36 |
| 5-Jan |
Beach toys |
11 |
=SUM($C$2:$C9) |
47 |
| 5-Jan |
Sandbox |
1 |
=SUM($C$2:$C10) |
48 |
| 5-Jan |
Sunglasses |
5 |
=SUM($C$2:$C11) |
53 |
| 5-Jan |
Swing set |
2 |
=SUM($C$2:$C12) |
55 |
|
Note If you look closely at the formulas in column D, you will see that the first part of the range is an absolute reference (both the column and row are preceded by the $ symbol), and that the second part of the range is a mixed reference (only the column is preceded by the $ symbol). Entering the formula in this way enables it to work as a running total when you copy it to adjacent rows.
- To maintain the running total, add a row for each new entry and copy the formula from column D to that row.
Note A running total differs from a running balance, in which you watch the sum of values grow or shrink (for example, a checkbook register) as new entries are added. For more information, see Calculate a running balance.
To learn about creating subtotals and grand totals, see Insert subtotals in a list of data in a worksheet.
