Counting the Number of Cells in an Excel Range

I recently saw a post on LinkedIn that used the + sign in front of a range reference.

On a blank range this converts all the blanks to zeroes. We can use this to count the number of cells in the range.

In the image above the yellow range is empty and as you can see the COUNTA function in cell A1 doesn’t count anything in the range.

A simple change to the range reference enables us to count the number of cells in the range – see image below.

Adding the + sign converts blank cell into zeroes and COUNTA counts all the zeroes.

Note the technique ignores if merge and center has been used in the range. Its still counts all the cells.

In the image below I have applied the merge and center to row 3 in the yellow range and it doesn’t change the calculation.