# Range determined using Cell Function

#### stoutbn

##### Board Regular
I am trying to do a countif with a range that may change columns regularly. Basically I want the range to be the first cell in a particular column, down to another cell within the same column. I'm trying to leverage the Cell() function in my formula, but excel doesn't approve of that method and keeps sending back an error.

Example:

I am trying to replace this formula:

Countif(\$B\$3:\$B3,B3)

This counts duplicates in a moving range.

Does anyone know how to make the Cell() method work or another way to do what I am trying to do? Thanks!

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Scott Huish

##### MrExcel MVP
I don't understand the first syntax you gave.

In your second COUNTIF formula, it's not clear what you are trying to do exactly. Are you trying to copy the formula down and have it increment columns or what?

#### stoutbn

##### Board Regular
Scott,

That's correct. By the nature of what I have to do, I need to copy/paste the formula 2 cells down into a table. There the formula copies to the bottom row of the column it is pasted in. So basically after a header, the first row of the table would be in cell B3. In that first row, I want the range of the countif to be from B3:B3 with a criteria of B3. But in the second row, for it to be B3:B4 with a criteria of B4. And so on. This is identifying whether a value has appeared yet in the table.

Please let me know if I need to explain further.

As far as the first syntax, I was attempting to substitute the B3, with the address of the cell using table references.

#### Scott Huish

##### MrExcel MVP
That's exactly what the formula you gave does when you copy it down.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">0</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >A1</td><td >=COUNTIF(\$B\$3:\$B3,B3)</td></tr><tr><td >A2</td><td >=COUNTIF(\$B\$3:\$B4,B4)</td></tr></table></td></tr></table> <br /><br />

Last edited:

#### stoutbn

##### Board Regular
Scott,

Exactly. That is the formula I am trying to replace by using table references. I am doing this because columns will be inserted in this table, possibly before column B, therefore the \$B, will then cause problems.

The Cell() function identifies the first cell in a table column (\$B\$3) and the [@[ symbol takes care of the \$B3 and B3 portions.

Does this make sense?

#### Scott Huish

##### MrExcel MVP
First time playing with tables, but it seems to know where the original column is even if you insert additional columns before it as it assigns a different column name to the new column when you insert the new one.

#### stoutbn

##### Board Regular
Scott,

That is all fine, but I am looking to make the Cell() function work because Excel is returning an error. This is necessary in the formula.

#### Scott Huish

##### MrExcel MVP
Cell Address will return a string so you may need to surround it with INDIRECT.

#### stoutbn

##### Board Regular
INDIRECT was the key! Thank you!

Replies
2
Views
195
Replies
6
Views
527
Replies
1
Views
163
Replies
11
Views
427
Replies
9
Views
210

1,190,862
Messages
5,983,272
Members
439,836
Latest member
BuckyBoyRx

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back