Range determined using Cell Function

[stoutbn]

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:

Countif(Cell("address",tbl1[Column1]):tbl1[@[Column1]],tbl1[@[Column1]]).

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!

 

[Scott Huish]

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]

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]

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 />

 

[stoutbn]

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]

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]

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]

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

 

[stoutbn]

INDIRECT was the key! Thank you!