# A formula to get total number of cells in an open-ended range

I need a formula to calculate the total number of cells in the range, including empty and not empty cells (i.e. the "size" of the range).

It is more useful for open-ended ranges, like `A:A`, `\$3:\$3`, or `A2:A`.

Actually the size of `A:A` is the same as the current number of rows in the sheet and the size of `\$3:\$3` is the current number of columns; so such formula could be used to determine the size of the sheet.

The formula would return a numeric value. I prepared a table with examples of returned values and an example screenshot.

``````Range  | Needed  | Notes
| formula |
| result  |
-----------------------------------------
A1:C3  |      9  | 3*3=9
A:A    |     11  | see example screenshot
\$3:\$3  |      5  | see example screenshot
A2:A   |     10  | see example screenshot
``````

``=COUNTIF(A:A,"<>")+COUNTIF(A:A,"")``
``=COUNTIF(A2:A,"<>")+COUNTIF(A2:A,"")``
``=COUNTIF(A:AAA,"<>")+COUNTIF(A:AAA,"")``
``=COUNTIF(2:2,"<>")+COUNTIF(2:2,"")``

• Thank you for your answer. Do you think it is faster than =COUNTA(ARRAYFORMULA(IF(A:A; TRUE; TRUE))) ? I am not sure, because it iterates the range twice, doing comparisons in the process... Mar 22, 2019 at 5:10
• it does however 0/1 evaluation is way too faster than storing value into memory like with array fx just to be counted again Mar 22, 2019 at 5:13
• anyway, this solution seems more readable than the one with ARRAYFORMULA() Mar 22, 2019 at 5:18

The answer was simple: use the built-in functions ROWS() and COLUMNS().

``````Range  | The needed formula          | Needed  | Notes
|                             | formula |
|                             | result  |
-----------------------------------------------------------------------
A1:C3  | =ROWS(A1:C3)*COLUMNS(A1:C3) |      9  | 3*3=9
A:A    | =ROWS(A:A)                  |     11  | see example screenshot
\$3:\$3  | =COLUMN(\$3:\$3)              |      5  | see example screenshot
A2:A   | =ROWS(A2:A)                 |     10  | see example screenshot
``````