Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
=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? . |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
inserting data from a row to a cell, when the row number is specified by a formula in a cell | New Users to Excel | |||
Formula Result Correct but value in the cell is wrong | Excel Worksheet Functions | |||
How can I write an if-then formula for 0 or less than 0 in cell t. | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |