Formulas to query a table

On this sheet, Table1 contains employee data.

Let’s run through some examples.

To start off, you’re free to use theROWS functionto count table rows.

This is the count of data rows only.

you could see we have 19 people in the list.

you’ve got the option to use theCOLUMNS functionto count columns.

To get a total count of table cells, you could use aformulawith both functions.

You could use a function like COUNTBLANK to count empty cells.

In this case, ID is a required value.

I use 103 as the function number and the ID column as a reference.

The number 103 tells SUBTOTAL to count values in visible rows only.

SUBTOTAL appears often with tables since it excludes filtered rows.

To get a value from the total row, you might use the #Totals specifier.

Just point and click.

Notice that Excel will return a #REF error if the Totals row is not visible.

If you want these to respond to the filter, use the SUBTOTAL function with 105, and 104.

Functions like COUNTIF and SUMIF also work well with tables.

I can easily get a count of each group with COUNTIF.