Excel has functions to count the number of rows and columns in a range. It doesn’t have a function to count the number of cells in a range. We can still perform the calculation with the COUNTA function.
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 cells 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.
Just to expand on that plus sign… it is my understanding it converts a range reference to an array of values… numbers and text just become their selves and blanks become zeros (just like =A1 would display 0 if A1 is blank). With that said, the method I would have used is to concatenate an empty text string onto the range and then use COUNTA on that…
=COUNTA(“”&B3:E9)
True, it makes the formula two characters longer, but it is more intuitive to me. Still, I’ll probably eventually adopt the plus sign method.
Hi Rick
That’s the way I convert real numbers into text numbers.
To me the double quotes with ampersand is more of a hack, whilst the + sign is a standard formula element.
Thanks for sharing an alternative.
Regards
Neale