CODEWITHSUNDEEP
excelexcel-formula
Abhay
Abhay

Reputation: 1

How to refer to a range of cells in Excel based on values from another cells

I am having trouble with this small thing in Excel.

Cell A1 = 10

Cell A2 = 20

There is a column C (in Sheet2) which has a long list of values.

I want to refer to a range of cells in column C based on values of A1 and A2, something like this:

=COUNTIF( Sheet2!C10 : Sheet2!C20 , ">=50")

Now, I do not want to hardcode 10 and 20 into the above formula. I want to get those values from cells A1 and A2. Please complete this formula for me:

=COUNTIF( Sheet2!C[Value of A1] : Sheet2!C[value of A2] , ">=50")

Upvotes: 0

Views: 55

Answers (1)

user10981853
user10981853

Reputation:

Use INDEX as the range/cell reference.

=COUNTIF(index(Sheet2!C:C, A1):index(Sheet2!C:C, A2), ">=50")

Upvotes: 3

Related Questions