Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: cell range referencing from within a function

  1. #1
    Guest

    Default

    hi,

    I have a cell which counts instances of a word in a range. The formula for that is:

    =COUNTIF(D529:D541,$F$5)

    F5 contains the word i'm counting

    This works fine, giving me the number of times the word in F5 appears in the given range. However, I am adding new data to this constantly and I want to be able to copy this formula easily. Basically I want to put "D529:D541" in a cell, say L10, and then reference this cell somehow from the formula. (sort of like, =COUNTIF(ref.L10,$F$5).

    Anyone know how I can do this fairly easily? I've looked at CELL("contents", L10), but it doesn't work when placed in the COUNTIF function (I think it doesn't return type range or something).

    All help appreciated,

    Lobo

  2. #2
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    odd, it logged me out while I was typing that..

    here's my login if that's needed

    Lobo

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-14 02:30, Anonymous wrote:
    hi,

    I have a cell which counts instances of a word in a range. The formula for that is:

    =COUNTIF(D529:D541,$F$5)

    F5 contains the word i'm counting

    This works fine, giving me the number of times the word in F5 appears in the given range. However, I am adding new data to this constantly and I want to be able to copy this formula easily. Basically I want to put "D529:D541" in a cell, say L10, and then reference this cell somehow from the formula. (sort of like, =COUNTIF(ref.L10,$F$5).

    Anyone know how I can do this fairly easily? I've looked at CELL("contents", L10), but it doesn't work when placed in the COUNTIF function (I think it doesn't return type range or something).

    All help appreciated,

    Lobo
    Lobo,

    What you are looking for is:

    =COUNTIF(INDIRECT($L$10),$F$5)

    Aladin

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks that worked perfectly

    I think it should probably come up when you search for referencing cells in excel help...

    thanks again for the help

    Lobo

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •