Countif #Value! error?

[HWL]

Using Excel 2007,
I'm doing a simple function, =countif(range, row), which will return a 1 if the data in that row is not a duplicate in range and a number depending on the times it is duplicated in the range. But on some of them I get a #value! error. Why is that? I mean, it should return at least as a 1 right?

Thanks

**POSSIBLE ANSWER** If the string is too long???

 

[T. Valko]

Using Excel 2007,
I'm doing a simple function, =countif(range, row), which will return a 1 if the data in that row is not a duplicate in range and a number depending on the times it is duplicated in the range. But on some of them I get a #value! error. Why is that? I mean, it should return at least as a 1 right?

Thanks

Can you post some data so we can see what you're trying to do?

 

[NBVC]

What is the Countif formula you are using?

 

[HWL]

What is the Countif formula you are using?

=COUNTIF($BN$7:$BN$671,BN7)

After further testing, the issue was that the string was too long. I think it can only handled a string of about 250 characters before countif fails and thus returns #value!

 

[NBVC]

One way around it is to use SUMPRODUCT

=SUMPRODUCT(($BN$7:$BN$671=BN7)+0)

 

[T. Valko]

=COUNTIF($BN$7:$BN$671,BN7)

After further testing, the issue was that the string was too long. I think it can only handled a string of about 250 characters before countif fails and thus returns #value!

255 characters is the limit.

256 or more characters and you'll get the #VALUE! error.

 

[txwizard]

I came to this four your old thread in search of answers regarding an unexpected #VALUE! error that puzzled me, since I have used COUNTIF against some very large ranges for many years.

255 characters is the limit.

256 or more characters and you'll get the #VALUE! error.

Seeing this remark prompted me to perform an experiment.

1. Create a large contiguous named range, composed entirely of cells in one column.
2. Fill most of the cells in the range with dummy text.
3. Insert a new column to the right of the range, to protect the work in the adjacent cells.
4. Fill the cells next to the ones in the range with the following formula:

   LEN(Br)

   , where

   r

   is the number of the row in which both cells reside.
5. Just below the column of cells containing the length formulas, add a formula to sum the range.
6. Observe the formula to the left of the sum, which is a countif against the test range, with the criterion being the current cell.

The formula shows that the cells within the range contain well over 255 characters, leading me to conclude that the 255 character limit must apply to the

COUNTIF

criterion (the second argument).

Related experiments lead to the corollary that there is a second reason, heretofore undocumented, for

COUNTIF

to return

#VALUE!

, which is that the range is not composed of contiguous cells. In my case, the range that consistently produces the error is

='Controls on Form'!$B$2:$B$16,'Controls on Form'!$P$2:$P$16

. In case I need it, I shall be saving a copy of the worksheet that demonstrates the issue.