How to Count Rows and Columns in a Range in Excel

Use ROWS and COLUMNS to measure filtered lists and rows of results — and to pick the last item of a dynamic list.

You will learn to

• Count the rows a FILTER returns with ROWS
• Count matching columns in a horizontal range with COLUMNS
• Use ROWS as a position to fetch the last item of a filtered list

How it works

ROWS(array) returns the number of rows in a range or array and COLUMNS(array) the number of columns: =ROWS(A2:A10) is 9 and =COLUMNS(E1:J1) is 6. They count positions, not values, so blanks count too.

Their real power shows with formulas that return arrays. =ROWS(FILTER(A2:A10,B2:B10="Blue")) counts how many students are in the Blue group, and =INDEX(list,ROWS(list)) returns the last item of any list, however long it grows. COLUMNS does the same for results laid out across a row.

=ROWS(FILTER(A2:A10,B2:B10=E4))