Hopefully I can make this clear. I have a formula in which I have to
change the range in which I am performing a COUNTIF.

Here's the formula.

=(COUNTIF($J8:$J28,"A"))+(COUNTIF($J8:$J28,"C"))

Essentially what I am doing is copying this formula down the
worksheet. What it is doing is looking at the next 21 line items and
counting how many are equal to A or C. That part works fine and I've
got everything going great.

Problem is, I want to dynamically change the number of line items I
perform the lookup on. Essentially I want to have cell A1 contain a
value that changes the number of rows that I want to look at.

So, if I have the value of 5 in cell A1, I want to change my COUNTIF
range from $J8:$J12.

Can this be done?

Thanks,
Dennis

Dennis explained on 3/10/2011 :
> Hopefully I can make this clear. I have a formula in which I have to
> change the range in which I am performing a COUNTIF.
>
> Here's the formula.
>
> =(COUNTIF($J8:$J28,"A"))+(COUNTIF($J8:$J28,"C"))
>
> Essentially what I am doing is copying this formula down the
> worksheet. What it is doing is looking at the next 21 line items and
> counting how many are equal to A or C. That part works fine and I've
> got everything going great.
>
> Problem is, I want to dynamically change the number of line items I
> perform the lookup on. Essentially I want to have cell A1 contain a
> value that changes the number of rows that I want to look at.
>
> So, if I have the value of 5 in cell A1, I want to change my COUNTIF
> range from $J8:$J12.
>
> Can this be done?
>
> Thanks,
> Dennis


=(COUNTIF(J8:OFFSET($J8,$A$1-1,0),"A")+(COUNTIF($J8:OFFSET($J8,$A$1-1,0),"C")))

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc