How to use variable range in Google Sheets?

Question

Currently I have such formula: COUNTIFS(B3:B36,"16",E3:E36,"01")

Would it be possible to turn these ranges B3:B36 and E3:E36 into variables, like B'start_cell_value':B'stop_cell_value'.

The whole thing would look like:

 =COUNTIFS(B'start_cell_value':B'stop_cell_value',"16",E'start_cell_value':E'stop_cell_value',"01")

start_cell_value and stop_cell_value are just some numbers stored in a separate cell. This would help, since changing numbers in those cells only would also change everything in the formula and that's exactly what I want.

I have tried to combine a numeric value from other cells with a letter to make a valid cell name but it doesn't seem to work or it just throws a reference error.

Any ideas would be appreciated.

Answer 1

If you have the start_cell_value in cell A1 and the stop_cell_value in A2 then try this formula:

=COUNTIFS(INDIRECT("B"&A1&":B"&A2),"16",INDIRECT("E"&A1&":E"&A2),"01")

Answer 2

with Named Ranges you can have it even exact:

=COUNTIFS(INDIRECT("B"&start_cell_value&":B"&stop_cell_value), "16", 
          INDIRECT("E"&start_cell_value&":E"&stop_cell_value), "01")

=COUNTIFS(INDIRECT("B"&A1&":B"&A2), "16",
          INDIRECT("E"&A1&":E"&A2), "01")