How to set cells range dynamically in Google Sheets?

0
votes

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?

1
google-sheetsgoogle-sheets-formula

1 Answers

1
votes

you need to INDIRECT it:

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