A single formula can enable your users to track daily sales totals. For example, say management wants to be able to determine the total sales receipts for each product category any day of the month. Totals of daily product sales receipts are summarized in the worksheet shown in Figure A.
Figure A
Two sales figures are given for each date: one at the end of the night shift at 6:00 AM and one at the end of the day shift at 6:00 PM. When a manager enters a date in H2, we want to display the totals for each product in cells I5:I8. Here are the steps:
- Select cells I5:I8.
- Enter the following formula:
- =SUMIF($A$2:$A$21,$H$2,OFFSET($A$2:$A$21,0,MATCH(H5,$1:$1,)-1))
- Press Ctrl + Enter.
The worksheet in Figure B shows the sales results for October 5.
Figure B
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.
