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

0

2 Answers 2

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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