using content of a cell in a formula in another cell
 

I am trying to use the value on one cell to form the range part an a countif
formula in another cell like this:
=COUNTIF(Actions!I2:I&"C2","*all*")

where C2 is the cell in the current worksheet whose contents (currently 20)
I want to use. The contents will change periodically. The formula that I
want evaluated would look like:
=COUNTIF(Actions!I2:I20,"*all*")

Anyone have a suggestion, please?

=COUNTIF(INDIRECT("Actions!I2:I"&C2),"*all*")

or

=COUNTIF(OFFSET(I2,,,C2-1),"*all*")

HTH
Jason
Atlanta, GA

-----Original Message-----
I am trying to use the value on one cell to form the
range part an a countif
formula in another cell like this:
=COUNTIF(Actions!I2:I&"C2","*all*")

where C2 is the cell in the current worksheet whose
contents (currently 20)
I want to use. The contents will change periodically.
The formula that I
want evaluated would look like:
=COUNTIF(Actions!I2:I20,"*all*")

Anyone have a suggestion, please?
.

A couple of ways

=COUNTIF(OFFSET(Actions!$I$2,,,C2+1,),"*all*")

=COUNTIF(INDEX(Actions!I2:I10000,):INDEX(Actions!I 2:I10000,C2),"*all*")

the latter is not volatile although a bit longer, it can also be written as

=COUNTIF(INDEX(Actions!I:I,2):INDEX(Actions!I:I,C2 +2),"*all*")

where the 2 and +2 indicates the range starts in row 2 of column I

Regards,

Peo Sjoblom

"mpierre" wrote:

I am trying to use the value on one cell to form the range part an a countif
formula in another cell like this:
=COUNTIF(Actions!I2:I&"C2","*all*")

where C2 is the cell in the current worksheet whose contents (currently 20)
I want to use. The contents will change periodically. The formula that I
want evaluated would look like:
=COUNTIF(Actions!I2:I20,"*all*")

Anyone have a suggestion, please?

Jason, Peo:
All your suggestions work great. Thank you!!

"Jason Morin" wrote:

=COUNTIF(INDIRECT("Actions!I2:I"&C2),"*all*")

or

=COUNTIF(OFFSET(I2,,,C2-1),"*all*")

HTH
Jason
Atlanta, GA

-----Original Message-----
I am trying to use the value on one cell to form the
range part an a countif
formula in another cell like this:
=COUNTIF(Actions!I2:I&"C2","*all*")

where C2 is the cell in the current worksheet whose
contents (currently 20)
I want to use. The contents will change periodically.
The formula that I
want evaluated would look like:
=COUNTIF(Actions!I2:I20,"*all*")

Anyone have a suggestion, please?
.