Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a limit on characters allowed | Excel Discussion (Misc queries) | |||
characters object limit | Excel Discussion (Misc queries) | |||
How to limit cell to 30 characters or less | Excel Discussion (Misc queries) | |||
Need help on countif and sumif function with dates and wildcard characters | Excel Discussion (Misc queries) | |||
Can I limit a cell to 2 characters? | Excel Discussion (Misc queries) |