Microsoft Excel Functions & Formulas

Use the SUM, OFFSET, MAX, IF, and ROW

functions to sum the last row in a dynamic list

Figure 11-14 shows a list that is updated constantly. The task here

is to determine the last row and sum up its entries. Use the MAX

and ROW functions to detect the last used row, then sum that row

with help from the SUM and OFFSET functions. Combine all these

functions in one array formula and assign the calculated result to

cell H2.

4

To sum the last row in a dynamic list:

1. In cells A2:A11 enter dates.

2. In cells B2:F11 list numbers for each team.

3. Select cell H2 and type the following array formula:

=SUM(OFFSET(B1:F1,MAX(IF(B1:F100<>"",

ROW(1:100)))-1,)).

4. Press ...