cell range referencing from within a function

Mar 14th, 2002, 03:30 AM

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 · Mar 14th, 2002, 03:32 AM

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

here's my login if that's needed

Lobo

Aladin Akyurek · Mar 14th, 2002, 04:24 AM

Quote:

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

lobo · Mar 15th, 2002, 02:14 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 14th, 2002, 03:30 AM	#1
Guest Posts: n/a	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

Mar 14th, 2002, 03:32 AM	#2
lobo New Member Join Date: Mar 2002 Posts: 2	odd, it logged me out while I was typing that.. here's my login if that's needed Lobo

Mar 15th, 2002, 02:14 AM	#4
lobo New Member Join Date: Mar 2002 Posts: 2	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