max limit to characters in COUNTIF function?

J

jen_writer

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
 
D

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
 
J

jen_writer

Beautiful! thank you!!

Dave Peterson said:
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
 

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