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?
--
Regards,
Booey

Maybe you should be using SUMPRODUCT
Tell us more about the problems
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Booey" wrote in message
...
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?
--
Regards,
Booey

You should have posted the formula you're trying.

Use SUMPRODUCT

Something like:

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

Or

A1 = text

=SUMPRODUCT(--(range=A1))

--
Biff
Microsoft Excel MVP


"Booey" wrote in message
...
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?
--
Regards,
Booey

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