Microsoft Office Tutorials and References
In Depth Information
To count only the visible data rows, use the SUBTOTAL or AGGREGATE function. For example, you can use
either of these formulas:
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:
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:
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
Returns the range that includes the Header row, all data rows, and the Total row.
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.
Returns the range that includes the Total row only. Returns a #REF! error if the
table has no Total row.
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