Thanks:  0
Likes:  0

# Thread: cell range referencing from within a function

1. 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. odd, it logged me out while I was typing that..

here's my login if that's needed

Lobo

3. 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)

4. 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

## User Tag List

#### Posting Permissions

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