Microsoft Office Tutorials and References

In Depth Information

**Working with Tables**

Figure 9-14:
The Formula AutoComplete feature is useful when creating a formula that refers to data in a

table.

Using this structured table syntax is optional — you can use actual range references if you like.

For example, the following formula returns the same result as the preceding one:

=SUMIF(B3:B8,”Jan”,D3:D8)

To refer to a cell in the Total row of a table, use a formula like this:

=Table2[[#Totals],[Sales]]

If the Total row in Table2 is not displayed, the preceding formula returns a #REF error.

This formula returns the value in the Total row of the Sales column in Table2.

To count the total number of rows in Table2, use the following formula:

=ROWS(Table2[#All])

The preceding formula counts all rows, including the Header row and Total row. To count only

the data rows, use a formula like this:

=ROWS(Table2[#Data])

A formula that’s in the same row as a table can use a #This Row reference to refer to table data

that’s in the same row. For example, assume the following formula is in row 3, in a column

outside Table2. The formula counts the number of entries in row 3 of Table2:

=COUNTA(Table2[@])