MrExcel Publishing
Your One Stop for Excel Tips & Solutions

COUNTIF Formula HELP!!!!

Posted by George on March 06, 2001 6:42 AM


I am trying to use the following formula:

Although as the number of cells will change on a daily basis, I need the user to highlight the cells and then use that range of cells in the formula.

Any ideas on how to do this? maybe a macro?



Posted by Dave Hawley on March 06, 2001 6:48 AM

Hi George

You could use a macro for this, but I think you should consider the use of a dynamic named range. If you follow my link to my web site, you will see a link for "Dynamic Named Ranges". There are some examples and instructions for them here.

OzGrid Business Applications

Posted by Aladin Akyurek on March 06, 2001 7:52 AM

Activate the option Insert|Name|Define, type DataRange or what suits you best as name for Names in Workbool and the following formula for Refers to:


where x is the name of the sheet whose column B you use for data entry.

Click OK. DataRange is a dynamic range, meaning that you can add/remove data points to column B at will. You must have in column B nothing but user-data which you want to average (in a certain way).

I would suggest in addition that you put the number in a cell, say in C2, select this cell, and
name this cell, e.g., Crit via the Name Box. Now you can rewrite your formula for averaging: