Subtotal It! (Issue 73) by Tiamari

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!

FIRST GET SORTED

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.

HOW TO DO IT

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.

NAME IT

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.

FORMAT IT

I use conditional formatting to highlight the totals in a bright colour. For a range starting on cell B4, this is the formula:

=ISNUMBER(FIND("Total";$B4))

NEED HELP?

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@tiamari.com or WhatsApp me on 061-341-9072.

Remember that you can catch up on previous issues on the WUEC website (www.wuec.co.za) or on www.tiamari.com.

Happy Clicking!