Count rows and columns with Excel

Posted in Applications -

Want to count rows and/or columns in a range with Excel no matter what content they contain? This post shows you how.

Count rows in Excel

Use this formula, where you want the number of rows selected from e.g. A1 to A10

=ROWS(A1:A10)

This example will show 10 in the cell where the formula is.

Count cells in Excel

Use this formula, where you want the number of columns selected from e.g. A1 to E1

=COLUMNS(A1:E1)

This example will show 5 in the cell where the formula is.

What happens when running the formulas against a block?

Note that the formulas will count the number of rows or columns, no matter what the selection. If we ran both rows() and columns() on the range A1 to E10, it would show 10 rows and 5 columns.

=ROWS(A1:E10)

ouputs 10

=COLUMNS(A1:E10)

outputs 5

Why?

Yes, selecting 5 cells from A to E is obviously 5 columns, but I personally manage a very basic ad booking system using a spreadsheet that is very very wide and I need to know numbers of days an ad is booked for. Using these functions makes it a lot easier, especially when it's running across cells FB17 to GS17. No, it's not the best ad booking system, but it works :)

And I'm sure you have your own reasons for needing to use these formulas, otherwise you wouldn't have been looking for this post.




Comments