Vary the Range in a Function

[carl]

I use this function

=COUNTIF(Sheet2!C$324:C$344;Sheet3!$D5)

I am looking for a good way to vary the range C$324:C$344 based on a number
I input in anther cell.

Lets say in a1 I have the value 20. I would like the range to be C$324:C$344.

If I change a1 to 50. I would like the range to be C$294:C$344.

Basicaly take the end of the range - C$344 - and subtract the number in a1.

Is this possible.

Thank you in advance.

 

[JE McGimpsey]

One way:

=COUNTIF(OFFSET(SHEET2!$C$324,0,0,A1+1;1);Sheet3!$D5)

 

[PCLIVE]

Maybe another way:

=COUNTIF(INDIRECT("Sheet2!" & H1 & ":" & I1),Sheet3!$D5)

H1 and I1 contains the desired range (ex. C$324 and C$344)

HTH,
Paul

 

[RagDyeR]

Another way ... non-volatile:

Enter start row in A1,
And end row in A2.

=COUNTIF(INDEX(Sheet2!C:C,A1):INDEX(Sheet2!C:C,A2),Sheet3!$D5)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I use this function

=COUNTIF(Sheet2!C$324:C$344;Sheet3!$D5)

I am looking for a good way to vary the range C$324:C$344 based on a number
I input in anther cell.

Lets say in a1 I have the value 20. I would like the range to be
C$324:C$344.

If I change a1 to 50. I would like the range to be C$294:C$344.

Basicaly take the end of the range - C$344 - and subtract the number in a1.

Is this possible.

Thank you in advance.