Countif Limitations

B

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?
 
T

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)
 
B

Bernard Liengme

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

T. Valko

You should have posted the formula you're trying.

Use SUMPRODUCT

Something like:

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

Or

A1 = text

=SUMPRODUCT(--(range=A1))
 
B

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


teylyn said:
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)


Booey;651216 said:
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

.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top