Prev: replace a single character WITH FORMAT in a cell
Next: My Excel operates as if Ctrl is always depressed. Help!
From: Booey on 22 Feb 2010 21:07
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
From: Bernard Liengme on 22 Feb 2010 21:20
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" <Booey(a)discussions.microsoft.com> wrote in message
news:BF7A7AD2-8F5D-490A-897D-23F9DD120181(a)microsoft.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?
> --
> Regards,
> Booey

From: T. Valko on 22 Feb 2010 21:23
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" <Booey(a)discussions.microsoft.com> wrote in message
news:BF7A7AD2-8F5D-490A-897D-23F9DD120181(a)microsoft.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?
> --
> Regards,
> Booey


From: teylyn on 22 Feb 2010 21:15

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)



B
o
o
e
y
;
6
5
1
2
1
6

W
r
o
t
e
:


>
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


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

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

From: Booey on 23 Feb 2010 02:18
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" wrote:

>
> 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 Wrote:
> >
> 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
>
>
> --
> 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
>
> [url="http://www.thecodecage.com"]Microsoft Office Help[/url]
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: replace a single character WITH FORMAT in a cell
Next: My Excel operates as if Ctrl is always depressed. Help!