Change Range In COUNTIF Formula Based On Value In A Cell

[Dennis]

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

 

[GS]

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")))