0

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

example screenshot

1

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

0

  • 1
    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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.