CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
someserj
someserj

Reputation: 47

How to set cells range dynamically in Google Sheets?

I need to pass a range into function dynamically. Starting from the specified row number till the end of column A

I'm using an ADDRESS function for it (it returns $A$10 in my case), but the value which is returned by ADDRESS is not working as the first parameter in a range.

=COUNTIFS(
   ADDRESS(ROW(OFFSET(D7,COUNTA(D7:D),0)),1) : A,
   "H"
)

Actual Result: COUNTIFS return 0.

Expected Result: it's supposed to return a number of cells with "H" value (I have more than 0 of them, of course)

I guess that such range combination (ADDRESS + :A) doesn't work. How to set the range correctly?

Upvotes: 0

Views: 261

Answers (1)

player0
player0

Reputation: 1

you need to INDIRECT it:

=COUNTIFS(INDIRECT(ADDRESS(ROW(OFFSET(D7, COUNTA(D7:D), 0)), 1, 4)&":A"), "H")

Upvotes: 1

Related Questions