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

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

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
| improve this answer | |

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.