Microsoft Excel Functions & Formulas

Use the OFFSET function to consolidate sales

for a day

This tip shows an effective way of summing all the sales of each

team for one specific day. The tricky part of the task is that the

dates appear more than once. To calculate all sales for each team on

one specific date, use the OFFSET function in combination with

SUMIF.

4

To consolidate sales per day and team:

1. In a worksheet, copy cells A1:E12, as shown in Figure 9-18.

2. In cell H1 enter a desired date.

3. In cells G3:G6 type the team names.

4. Select cells H3:H6 and type the following formula:

=SUMIF($A$2:$A$12,$H$1,OFFSET($A$2:$A$12,0,

MATCH(G3,$1:$1,)-1)).

5. Press <Ctrl+Enter>.

234

Chapter 9

Figure ...