Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Count cells with length not equal to 7
I want to count the number of cells in a column that do not have a value that
is 7 digits in length. I've tried several combinations of CountIf and Len and have failed miserably! Can anyone help? |
#2
|
|||
|
|||
Try
=SUMPRODUCT(--(LEN(TRIM(A1:A10))<7)) Regards, Peo Sjoblom "cottage6" wrote: I want to count the number of cells in a column that do not have a value that is 7 digits in length. I've tried several combinations of CountIf and Len and have failed miserably! Can anyone help? |
#3
|
|||
|
|||
try
=SUMPRODUCT((LEN(A2:A22)<7)*1) -- Don Guillett SalesAid Software "cottage6" wrote in message ... I want to count the number of cells in a column that do not have a value that is 7 digits in length. I've tried several combinations of CountIf and Len and have failed miserably! Can anyone help? |
#4
|
|||
|
|||
Thanks to both Peo and Don whose formulas solved my problem. One additional
question; can I leave blank cells out of the count? "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(LEN(TRIM(A1:A10))<7)) Regards, Peo Sjoblom "cottage6" wrote: I want to count the number of cells in a column that do not have a value that is 7 digits in length. I've tried several combinations of CountIf and Len and have failed miserably! Can anyone help? |
#5
|
|||
|
|||
sure. just add the
a1:a10<0 parameter =SUMPRODUCT((LEN(TRIM(A1:A10))<7)*(a1:a10<)) -- Don Guillett SalesAid Software "cottage6" wrote in message ... Thanks to both Peo and Don whose formulas solved my problem. One additional question; can I leave blank cells out of the count? "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(LEN(TRIM(A1:A10))<7)) Regards, Peo Sjoblom "cottage6" wrote: I want to count the number of cells in a column that do not have a value that is 7 digits in length. I've tried several combinations of CountIf and Len and have failed miserably! Can anyone help? |
#6
|
|||
|
|||
Don, thanks a lot. I'm quite jealous of your logic!
"Don Guillett" wrote: sure. just add the a1:a10<0 parameter =SUMPRODUCT((LEN(TRIM(A1:A10))<7)*(a1:a10<)) -- Don Guillett SalesAid Software "cottage6" wrote in message ... Thanks to both Peo and Don whose formulas solved my problem. One additional question; can I leave blank cells out of the count? "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(LEN(TRIM(A1:A10))<7)) Regards, Peo Sjoblom "cottage6" wrote: I want to count the number of cells in a column that do not have a value that is 7 digits in length. I've tried several combinations of CountIf and Len and have failed miserably! Can anyone help? |
#7
|
|||
|
|||
On Thu, 7 Apr 2005 08:17:13 -0700, "cottage6"
wrote: I want to count the number of cells in a column that do not have a value that is 7 digits in length. I've tried several combinations of CountIf and Len and have failed miserably! Can anyone help? Here's one way: Assume your data starts in B1. Enter =IF(LEN(B1)<7,0,1) in C1 & copy it down. Then Sum column C. I'm sure you'll get several answers, some probably cleaner than this one, however, it may get you on your way for now. Don S |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I count how many grey-filled cells are in a row ? | Excel Worksheet Functions | |||
How can I count cells that meet two criteria within a filtered co. | Excel Worksheet Functions | |||
Count number of shaded cells | Excel Discussion (Misc queries) | |||
Count cells with data | New Users to Excel | |||
How to verify that 3 cells are equal | Excel Worksheet Functions |