Because tables support structured references, you can learn a lot about a table with basic formulas. In this video, we run through about a dozen examples. It's a nice demo of how formulas and tables work together to save you time.
In this video, we'll look at some formulas you can use to query a table.
Because tables support structured references, you can learn a lot about a table with basic formulas.
On this sheet, Table1 contains employee data. Let's run through some examples.
To start off, you can use the ROWS function to count table rows. This is the count of data rows only. You can see we have 19 people in the list.
The Excel ROW function returns the row number for a reference. For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula.
The Excel SUBTOTAL function returns an aggregate result for supplied values. SUBTOTAL can return a SUM, AVERAGE, COUNT, MAX, and others (see table below), and SUBTOTAL function can either include or exclude values in hidden rows.
COUNTIF is a function to count cells that meet a single criteria. COUNTIF can be used to count cells with dates, numbers, and text that match specific criteria. The COUNTIF function supports logical operators (>,...