Dynamic Ranges
 

Hi, Following previous advice, the following formula returns the % "Yes" in a
range from J84:J**; ** being referenced from a value in cell A2. Is it
possible to now tweek this formula so that the first part of the range
(J84)is also remotely referenced from a value in say B2 ? i.e. J"&B2:J"&A2 ?

=COUNTIF(INDIRECT("J84:J"&A2),"Yes")/COUNTA(INDIRECT("J84:J"&A2))*100

Many Thanks

Dynamic Ranges
 

Try this:

=COUNTIF(INDIRECT("J"&B2&":J"&A2),"Yes")/COUNTA(INDIRECT("J"&B2&":J"&A2))*100

HTH,
Elkar



"Graham" wrote:

Hi, Following previous advice, the following formula returns the % "Yes" in a
range from J84:J**; ** being referenced from a value in cell A2. Is it
possible to now tweek this formula so that the first part of the range
(J84)is also remotely referenced from a value in say B2 ? i.e. J"&B2:J"&A2 ?

=COUNTIF(INDIRECT("J84:J"&A2),"Yes")/COUNTA(INDIRECT("J84:J"&A2))*100

Many Thanks

Dynamic Ranges
 

=COUNTIF(INDIRECT("J" & B2 &":J"&A2),"Yes")/COUNTA(INDIRECT("J" & B2
&":J"&A2))*100

"Graham" wrote:

Hi, Following previous advice, the following formula returns the % "Yes" in a
range from J84:J**; ** being referenced from a value in cell A2. Is it
possible to now tweek this formula so that the first part of the range
(J84)is also remotely referenced from a value in say B2 ? i.e. J"&B2:J"&A2 ?

=COUNTIF(INDIRECT("J84:J"&A2),"Yes")/COUNTA(INDIRECT("J84:J"&A2))*100

Many Thanks

Dynamic Ranges
 

Graham wrote...
Hi, Following previous advice, the following formula returns the %
"Yes" in a range from J84:J**; ** being referenced from a value in
cell A2. Is it possible to now tweek this formula so that the first
part of the range (J84)is also remotely referenced from a value in
say B2 ? i.e. J"&B2:J"&A2 ?

=COUNTIF(INDIRECT("J84:J"&A2),"Yes")/COUNTA(INDIRECT("J84:J"&A2))*100

Yes, but there's a better way to do this.

=COUNTIF(INDEX(J:J,B2):INDEX(J:J,A2),"Yes")
/COUNTA(INDEX(J:J,B2):INDEX(J:J,A2))*100

Better still would be to restict this to the largest possible range.
If that were J1:J1000,

=COUNTIF(INDEX(J1:J1000,B2):INDEX(J1:J1000,A2),"Ye s")
/COUNTA(INDEX(J1:J1000,B2):INDEX(J1:J1000,A2))*100

Then there's shorter.

=100*PRODUCT(COUNTIF(INDEX(J1:J1000,B2):INDEX(J1:J 1000,A2),
{"Yes","<"})^{1,-1})

Dynamic Ranges
 

Many thanks to you both!

"Elkar" wrote:

Try this:

=COUNTIF(INDIRECT("J"&B2&":J"&A2),"Yes")/COUNTA(INDIRECT("J"&B2&":J"&A2))*100

HTH,
Elkar



"Graham" wrote:

Hi, Following previous advice, the following formula returns the % "Yes" in a
range from J84:J**; ** being referenced from a value in cell A2. Is it
possible to now tweek this formula so that the first part of the range
(J84)is also remotely referenced from a value in say B2 ? i.e. J"&B2:J"&A2 ?

=COUNTIF(INDIRECT("J84:J"&A2),"Yes")/COUNTA(INDIRECT("J84:J"&A2))*100

Many Thanks

Dynamic Ranges
 

Thank-you to you too Harlan. When you say 'better way to do this', in what
context ? The previous works, would it be worth my while to change it to your
suggestion ? There often appear to be different ways of achieving the same
result in Excel ?

"Harlan Grove" wrote:

Graham wrote...
Hi, Following previous advice, the following formula returns the %
"Yes" in a range from J84:J**; ** being referenced from a value in
cell A2. Is it possible to now tweek this formula so that the first
part of the range (J84)is also remotely referenced from a value in
say B2 ? i.e. J"&B2:J"&A2 ?

=COUNTIF(INDIRECT("J84:J"&A2),"Yes")/COUNTA(INDIRECT("J84:J"&A2))*100

Yes, but there's a better way to do this.

=COUNTIF(INDEX(J:J,B2):INDEX(J:J,A2),"Yes")
/COUNTA(INDEX(J:J,B2):INDEX(J:J,A2))*100

Better still would be to restict this to the largest possible range.
If that were J1:J1000,

=COUNTIF(INDEX(J1:J1000,B2):INDEX(J1:J1000,A2),"Ye s")
/COUNTA(INDEX(J1:J1000,B2):INDEX(J1:J1000,A2))*100

Then there's shorter.

=100*PRODUCT(COUNTIF(INDEX(J1:J1000,B2):INDEX(J1:J 1000,A2),
{"Yes","<"})^{1,-1})