max limit to characters in COUNTIF function?

[Guest]

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]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

 

[Guest]

Beautiful! thank you!!

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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