Microsoft Office Tutorials and References

In Depth Information

=ROWS(Table2[#Data])

To count only the visible data rows, use the SUBTOTAL or AGGREGATE function. For example, you can use

either of these formulas:

=SUBTOTAL(103,Table2[Region])

=AGGREGATE(3,5,Table2[Region])

Because the formula is counting visible rows, you can use any column header in the preceding formula.

A formula that's not in the table but is in the same
row
as a table can use an @ reference to refer to table data

that is in the same row. For example, assume the following formula is in row 3, in a column outside Table2. The

formula returns the value in row 3 of the Sales column in Table2:

= Table2[@Sales])

You can also combine row and column references by nesting brackets and including multiple references separ-

ated by commas. The following example returns Sales from the current row divided by the total sales:

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

A formula like the preceding one is much easier to create if you use the pointing method.

Table 9-1 summarizes the row identifiers for table references and also describes which ranges they represent.

Table 9-1: Table Row References

Row

Identifier

Description

#All

Returns the range that includes the Header row, all data rows, and the Total row.

#Data

Returns the range that includes the data rows but not the Header and Total rows.

Returns the range that includes the Header row only. Returns a #REF! error if the

table has no Header row.

#Headers

Returns the range that includes the Total row only. Returns a #REF! error if the

table has no Total row.

#Totals

Represents “this row.” Returns the range that is the intersection of the formula's

row and a table column. If the formula row does not intersect with the table (or is

the same row as the Header or Total row) a #VALUE! error is returned.

@

Filling in the gaps