Excel, variable range for a countif() formula

austina · May 15th, 2002, 01:25 PM

I have a row of data,and wish to countif from the 1st entry to the nth where n is a variable dependent on other calculations.

I can display the range as text but if I reference the tect in the formula it doesn't like it I have tried T(),Text(), Value() etc but it just doesn't want to know.

FYI the formula for the range is ="B"&ROW()&":"&ADDRESS(ROW(),BJ4)where BJ4 is the value n (when n=9 this formula in row 4 gives B4:I4 my data is in Row 4 from B4 to ad4)

HELP!

[ This Message was edited by: austina on 2002-05-15 12:26 ]

Juan Pablo González · May 15th, 2002, 01:26 PM

Try with INDIRECT

austina · May 15th, 2002, 01:33 PM

OK...... Do I feel soooooo stoopid!!!!! I had tried Indirect already but it still didn't work. However the breath of fresh air and a new wall to bounce my head off did the trick.... my problem lay not with the range but with the criteria which SHOULD have been in Quotation marks. Thanks for the help.. I'll be back!

Aladin Akyurek · May 15th, 2002, 02:55 PM

Quote:

On 2002-05-15 12:25, austina wrote:
I have a row of data,and wish to countif from the 1st entry to the nth where n is a variable dependent on other calculations.

I can display the range as text but if I reference the tect in the formula it doesn't like it I have tried T(),Text(), Value() etc but it just doesn't want to know.

FYI the formula for the range is ="B"&ROW()&":"&ADDRESS(ROW(),BJ4)where BJ4 is the value n (when n=9 this formula in row 4 gives B4:I4 my data is in Row 4 from B4 to ad4)

HELP!

[ This Message was edited by: austina on 2002-05-15 12:26 ]

Did you consider using OFFSET instead?

May 15th, 2002, 01:26 PM	#2
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	Try with INDIRECT __________________ Regards, Juan Pablo González http://www.juanpg.com

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

May 15th, 2002, 01:25 PM	#1
austina New Member Join Date: May 2002 Posts: 2	I have a row of data,and wish to countif from the 1st entry to the nth where n is a variable dependent on other calculations. I can display the range as text but if I reference the tect in the formula it doesn't like it I have tried T(),Text(), Value() etc but it just doesn't want to know. FYI the formula for the range is ="B"&ROW()&":"&ADDRESS(ROW(),BJ4)where BJ4 is the value n (when n=9 this formula in row 4 gives B4:I4 my data is in Row 4 from B4 to ad4) HELP! [ This Message was edited by: austina on 2002-05-15 12:26 ]

May 15th, 2002, 01:33 PM	#3
austina New Member Join Date: May 2002 Posts: 2	OK...... Do I feel soooooo stoopid!!!!! I had tried Indirect already but it still didn't work. However the breath of fresh air and a new wall to bounce my head off did the trick.... my problem lay not with the range but with the criteria which SHOULD have been in Quotation marks. Thanks for the help.. I'll be back!