Stay current on all daily transactions with the help of Excel functions

With a handful of well-constructed formulas, you can keep on top of the latest information in your Excel worksheets.

By adding a few formulas to your worksheet, you can keep your employees up to date on the latest sales figures. For example, suppose you use the worksheet shown in Figure A to keep track of daily sales for the month.

Figure A

worksheet data

To show the Total Sales-To-Date, enter the labels and formulas shown in Figure B.

Figure B

sales formulas

The formula in E2 searches the data cells in the column for a match to today's date, calculated by the Today()function in E1. When Excel finds a cell whose value matches the date in E1, it returns the address of the cell to the right of the match. The SUM function uses the INDIRECT function to specify E2 as last cell in the range to be totaled.

You can perform different calculations by replacing the SUM with another function. For example, to find the average sales for the month up to today, replace SUM in the formula in E3 with AVERAGE.

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks

Free Newsletters, In your Inbox