Google Sheets – Define Range in Function Using Cell Value

formulasgoogle sheetsworksheet-function

I have the following table and I'm trying to write a formula that will take the value of column C and put it into the COUNTIF function where the ?? is. 

A                     B                         C
Data Label            Data Range                Name
----------            ----------                ----------
Label#1               =countif(data!??,A1)      A1:B
Label#2               =countif(data!??,A2)      C1:D
Label#3               =countif(data!??,A3)      E1:F

I have hundreds of rows and dozens of columns and I'm trying to avoid manually update the target range for the COUNTIF.

I've tried =countif(data!&C1,A1) and =countif(data!&"C1",A1) and =countif(data!C1,A1), but none work.

Best Answer

Use INDIRECT() to get a cell reference by a string, which in your case is coming from a cell. To use the range as defined in cell C2 but refer to a sheet called data, use:

=countif(INDIRECT("data!"&C2, TRUE),A2)

Hopefully I understand the final formula correctly. If not, replace the range reference with the INDIRECT() function. If this does not work, first check your formula with a manual entry of the range. If that works, there is a syntax issue with the INDIRECT() function so try putting

=INDIRECT(C2, TRUE)

in a cell and see what is returned to check that as well.

Related Topic