Exceljet

Quick, clean, and to the point

This video is part of our online video training.

How to query a table with formulas

Tags 
Summary 
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.
Video 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)

The COLUMNS function will do the same for 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, 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 for 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 available 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]]

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.

Author 
Dave Bruns