Change Range In COUNTIF Formula Based On Value In A Cell

  • Thread starter Thread starter Dennis
  • Start date Start date
D

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
 
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
Click to expand...


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