Countif Limitations

[Booey]

Hi,

I have a problem with Countif, and that is it seems to only work where the
criteria is 255 characters or less.

I have concatenated about 30 columns of data into the next available column
and wanted to use that column as the range and each cell within the range as
the criteria. Some of the concatenated cells have up to 400 characters in
them and anything over 255 characters is giving me a result of #VALUE!.

Is there some other function that might enable this?

 

[teylyn]

Hi,

you could have a go with Sumproduct. If required, you could break u
your concatenated column, since sumproduct accepts up to 32 (I think
arguments.

teylyn
'The Code Cage - Microsoft Office Help - Microsoft Office Discussion
(http://www.thecodecage.com)

 

[Bernard Liengme]

Maybe you should be using SUMPRODUCT
Tell us more about the problems
best wishes

 

[T. Valko]

You should have posted the formula you're trying.

Use SUMPRODUCT

Something like:

=SUMPRODUCT(--(range="text"))

Or

A1 = text

=SUMPRODUCT(--(range=A1))

 

[Booey]

Hi,

I had thought about using SUMPRODUCT, but didn't know if it has limitations
on the components in each array. The reason I am concatenating first is to
test for duplicates across each row of data. There are something like 7000
rows and almost 50 columns of data.

I had automated (macro) the whole thing using countif originally but have
since changed it to some nested for loops to test each cell against each
individual cell in the range.

It works fine but is quite slow. I know SUMPRODUCT is very resource
intensive, but I might give this a try as well, if the slowness becomes a
problem. If SUMPRODUCT has limitations like countif, it doesn't matter as I
have gotten around the problem.

Thanks for the suggestions.
--
Regards,
Booey

Hi,

you could have a go with Sumproduct. If required, you could break up
your concatenated column, since sumproduct accepts up to 32 (I think)
arguments.

teylyn
'The Code Cage - Microsoft Office Help - Microsoft Office Discussion'
(http://www.thecodecage.com)

Hi,

I have a problem with Countif, and that is it seems to only work where the
criteria is 255 characters or less.

I have concatenated about 30 columns of data into the next available column
and wanted to use that column as the range and each cell within the range as
the criteria. Some of the concatenated cells have up to 400 characters in
them and anything over 255 characters is giving me a result of #VALUE!.

Is there some other function that might enable this?

--
teylyn

Teylyn -- 'teylyn.posterous.com' (http://teylyn.posterous.com)
------------------------------------------------------------------------
teylyn's Profile: 983
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=181653

Microsoft Office Help

.

 

[Bernd P]

Hello,

I have a problem with Countif, and that is it seems to only work where the
criteria is 255 characters or less.

Yes, Countif and Sumif only accept 255 characters here.

[A German link: http://www.xlam.ch/xlimits/worksheetfunctions.htm]

As already suggested, you might want to use Sumproduct:
http://sulprobil.com/html/countif1.html

Regards,
Bernd