Vary the Range in a Function
 

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.

Vary the Range in a Function
 

One way:

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


In article ,
carl wrote:

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.

Vary the Range in a Function
 

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


"carl" wrote in message
...
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.

Vary the Range in a Function
 

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!
================================================== ===

"carl" wrote in message
...
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.