I would like to count the number of non-empty cells in a range that depends on the row number. I've tried counta(row()+3:row()+4) which always returns 1 as does counta(row():row()) (where I would have expected a circular dependency). counta(12:12) works fine, as does count(13:14).

How do I make a range that is dependent on row()?


You cannot construct a range specification like that. You'll want the =INDIRECT function (documentation), in order to translate a row number into a range.

You must also include the column in your range. If your range spans columns A to C, from row 1 to 3, the range should be A1:C3. This can be constructed from a string: "A" & 1 & ":C" & 3. Replacing the row numbers with row() from your question, we get "A" & row()+3 & ":C" & row()+4, which leads us to the final formula:

=COUNTA(INDIRECT("A" & row()+3 & ":C" & row()+4))
  • 1
    Thanks. Works, except that it doesn't need to include the column range. It can ignore the specific columns as well with COUNTA(Indirect((row()+3)&":"&(row()+4))). – Avi Ginsburg Oct 7 '15 at 10:56

The other answer mentions that the reference should include the column but this isn't this way nowadays, as a reference of the form 1:1 is valid in the current version (2019) of Google Sheets.

Anyway, the other answer is right regarding to use a formula to build a reference we should use INDIRECT.

=COUNTA(INDIRECT(ROW() + 3 &":"& ROW() + 4))


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.