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
 
Search JabSto ::




Custom Search