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.

1 Answer 1


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


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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.