Google Sheets – Formula to Get Total Number of Cells in Open-Ended Range

formulasgoogle sheets

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

Best Answer

=COUNTIF(A:A,"<>")+COUNTIF(A:A,"")

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

Related Solutions

Google-sheets – Scan row for value, get column of that match, apply column in another formula in a different cell

The match formula will tell you which column within 1:1 matches today(); in conjunction with index we can use that column number to get the address of the cell holding the Yes/No data for today. This becomes useful when paired through string concatenation (&) with the address of the first cell of the Yes/No data ($A$2 here) -- the 'dynamic' range you wanted can now be interpreted using the indirect formula!

All together, you're looking at the following formula for B3 in your example:

=COUNTIF(INDIRECT("$A$2:"&CELL("address",INDEX($1:$2,2,MATCH(TODAY(),$1:$1)))),"Yes")

edit

I temporarily forgot about filter, which makes this task much easier: =COUNTIF(FILTER(2:2,1:1<=TODAY()),"No")

Google Sheets – How to Block Automatic Row and Column Insertion

Short answer

Use ARRAY_CONSTRAIN(array,row_limit,column_limit)

Explanation

ARRAY_CONSTRAIN function could return the number of rows and columns that you want, just set the row_limit and column_limit parameters accordingly. To automatically set this parameters you could use a function like COUNT or COUNTA.

To calculate the number of rows/columns considering the data range by using built-in functions,

for rows use a formula like the following

=MAX(IF(LEN(A2:A)>0,ROW(A2:A)-1,0))

for columns use a formula like the following

=MAX(IF(LEN(1:1)>0,COLUMN(1:1)-1,0))

The sample formula in the question will look like the following:

=ARRAYFORMULA(
   ARRAY_CONSTRAIN(
     MMULT(N(ROW(A2:A)>=TRANSPOSE(ROW(A2:A))),SIGN(ROW(A2:A))),
     MAX(IF(LEN(A2:A)>0,ROW(A2:A)-1,0)),
     MAX(IF(LEN(1:1)>0,COLUMN(1:1)-1,0))
   )
)