max limit to characters in COUNTIF function?
 

I am using the formula =COUNTIF('Sheet 2'!$L$3:$L$325,AP8) to count the
number of times cell AP8 appears in the range. Cell AP8 contains text. It was
working just fine until I had to add a few sentences to cell AP8. Now I get
an error in the counting column.

Can the COUNTIF function not count occurances of cells with text that is too
long?
thanks
jen

max limit to characters in COUNTIF function?
 

But you can use a different formula:
=SUMPRODUCT(--('sheet 2'!$l$3:$l$325=ap8))

or if AP8 held a substring that you wanted to count:
=SUMPRODUCT(--(ISNUMBER(SEARCH(ap8,'sheet 2'!$l$3:$l$325))))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html




jen_writer wrote:

I am using the formula =COUNTIF('Sheet 2'!$L$3:$L$325,AP8) to count the
number of times cell AP8 appears in the range. Cell AP8 contains text. It was
working just fine until I had to add a few sentences to cell AP8. Now I get
an error in the counting column.

Can the COUNTIF function not count occurances of cells with text that is too
long?
thanks
jen

--

Dave Peterson

max limit to characters in COUNTIF function?
 

Beautiful! thank you!!

"Dave Peterson" wrote:

But you can use a different formula:
=SUMPRODUCT(--('sheet 2'!$l$3:$l$325=ap8))

or if AP8 held a substring that you wanted to count:
=SUMPRODUCT(--(ISNUMBER(SEARCH(ap8,'sheet 2'!$l$3:$l$325))))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html




jen_writer wrote:

I am using the formula =COUNTIF('Sheet 2'!$L$3:$L$325,AP8) to count the
number of times cell AP8 appears in the range. Cell AP8 contains text. It was
working just fine until I had to add a few sentences to cell AP8. Now I get
an error in the counting column.

Can the COUNTIF function not count occurances of cells with text that is too
long?
thanks
jen

--

Dave Peterson