Tuesday, October 25, 2005

Number of entries in a column or row in Excel

This one from Troy

If you want to know how many rows have entries in a particular column (works with columns as well as rows), create an array formula as follows:

# of rows with non blank cells
{=MAX(ROW($B:$B))-COUNTBLANK($B:$B)}

# of columns with non blank cells
{=MAX(COLUMN($2:$2))-COUNTBLANK($2:$2)}

(remember array formulas are created without the curly brackets and entered by holding down CTRL and SHIFT when hitting Enter)

Especially handy if you know that all the rows up to a certain point contain data yet you're not certain just how many rows there are and or your looking for the last cell in a column for use in a dynamic offset,
Simply doing {=MAX(ROW($B:$B))} would return the total number of cells 65,536

*NB
=OFFSET( ) only works if the target wBook is open (at least in Excel 2000)


Posted by: Jethro | 5:13 PM |