Sometimes it is the little things that makes our lives easier.
I manage a small, informal Excel account system for a local organization. One of the lists I keep is one where I am recording payments on a weekly basis.
In order to get the total for the week, I need a formula to calculate only that portion of the list pertaining to a specific date. Although I could enter a SUM-formula for each group of entries, it is not the most elegant solution.
A little-known Excel gem called Subtotal makes it so simple!
When using the subtotal feature, it is important that the list is sorted correctly. In this example, we want Excel to create a subtotal for all the payments on each separate date. Therefore the list must be sorted by the date column. Excel will insert a subtotal for each change in date value.
Select the whole table of data, including the headings and any open rows below. You will find the Subtotal icon on the DATA tab, within the Outline group. The Subtotal dialog box will open. For my Member Payments list, I would complete it as follows:
· At each change in: Date
· Use function: Sum (There are also other functions to choose from)
· Add subtotal to: Amount
· Replace current subtotals: Yes (ticked)
· Page break between groups: No
· Summary below data: Yes
Then hit the OK button, and… Whalaaaa!
Excel will calculate a neat subtotal at each date interval, as well as a Grand Total at the bottom. If you included any empty rows below your data, the Grand Total will be below these. You can remove the subtotals at any time with the Remove All button.
To make the selection easier in future, I name the range. To do this, you select the range. Then type a name for it in the Name Box. The Name Box is to the left of the formula bar - it contains the name of the cell or range i.e. A1. Enter a name of your choice, such as “MemberPayments”, and press Enter. Note that cell or range names cannot contain spaces.
I use conditional formatting to highlight the totals in a bright colour. For a range starting on cell B4, this is the formula:
Please feel free to book a session with me. I do software training on most of the popular Software packages. Send an email to email@example.com or WhatsApp me on 061-341-9072.
Showing all records: