Abstract
Transcript
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.
=ROWS(Table1)
You can use the COLUMNS function to count columns.
=COLUMNS(Table1)
To get a total count of table cells, you can use a formula with both functions.
=ROWS(Table1)*COLUMNS(Table1)
You could use a function like COUNTBLANK to count empty cells.
=COUNTBLANK(Table1)
To count visible rows only, you'll need to use the SUBTOTAL function referencing a column that does not contain empty cells.
In this case, ID is a required value. I use 103 as the function number and the ID column as a reference.
=SUBTOTAL(103,Table1[ID])
The number 103 tells SUBTOTAL to count values in visible rows only.
Now if I filter the table, the visible row count goes down, and when I clear the filter, it goes back up.
SUBTOTAL appears often with tables since it excludes filtered rows.
Sheet 3 in this workbook contains a full list of operations you can perform with SUBTOTAL, and a link to more information on our site.
To get a value from the total row, you can use the #Totals specifier. Just point and click.
=Table1[[#Totals],[Group]]
Notice that Excel will return a #REF error if the Totals row is not visible.
You can trap this error with IFERROR, and return an empty string if the total row is disabled.
=IFERROR(Table1[[#Totals],[Group]],"")
In a column with numeric data, like the start date column, you can use MIN and MAX to get the earliest and latest dates.
=MIN(Table1[Start])
=MAX(Table1[Start])
If you want these to respond to the filter, use the SUBTOTAL function with 105, and 104.
=SUBTOTAL(105,Table1[Start]) -- min
=SUBTOTAL(104,Table1[Start]) -- max
Functions like COUNTIF and SUMIF also work well with tables. I can easily get a count of each group with COUNTIF.
=COUNTIF(Table1[Group],I17)
As always, the best part about using a table for data is that the range is dynamic When I paste in more data, all formulas are instantly up to date, with no need to adjust any references.
