# 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... – Andrej Adamenko Mar 22 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 – user0 Mar 22 at 5:13
• anyway, this solution seems more readable than the one with ARRAYFORMULA() – Andrej Adamenko Mar 22 at 5:18

I managed to do it with the following formula.

``````=COUNTA(ARRAYFORMULA(A:A & "x"))
``````

I did not test it for performance, but I believe the following version should work faster.

``````=COUNTA(ARRAYFORMULA(IF(A:A; TRUE; TRUE)))
``````
• performance vice, this is slow because arrayformula will first need to print "x" or TRUE in a virtual column and then counta will count it – user0 Mar 22 at 4:47
• I do not know the implementation of ARRAYFORLMULA(), but I believe the virtual column does not need to be physically created. As far as implementation goes, ARRAYFORMULA() could be internally converted to a loop providing values to COUNTA() one by one – Andrej Adamenko Mar 22 at 5:14
• if that would be the case then insanely huge arrays wouldn't slow down a spreadsheet. kinda doesn't matter where are values imprinted - if in real cells or in memory/VR column – user0 Mar 22 at 5:25
• of course it does matter where the cells are located. I am sure that the real cells are slower than VR column. I mean if we use it like this, =ARRAYFORMULA(IF(A:A; TRUE; TRUE)) in cell B1, actually creating real cells and then use =COUNTA(B:B) to count them it will be much slower then using ARRAYFORMULA() inside of COUNTA() – Andrej Adamenko Mar 22 at 5:32
• yes, if put that way... but when compared with countif it doesn't matter anymore coz if printed array can do it in 10 seconds, VR array can do it in 7 seconds but countif will manage it in 1 second (just a rough example) – user0 Mar 22 at 5:37