Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
cottage6
 
Posts: n/a
Default 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   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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?

  #4   Report Post  
cottage6
 
Posts: n/a
Default

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   Report Post  
Don S
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I count how many grey-filled cells are in a row ? AnthonyG Excel Worksheet Functions 1 February 4th 05 11:08 AM
How can I count cells that meet two criteria within a filtered co. lizzzy Excel Worksheet Functions 1 January 21st 05 07:03 PM
Count number of shaded cells Maddoktor Excel Discussion (Misc queries) 2 December 20th 04 09:35 PM
Count cells with data shoiley New Users to Excel 5 November 28th 04 08:23 PM
How to verify that 3 cells are equal Scott Excel Worksheet Functions 1 November 9th 04 11:24 PM


All times are GMT +1. The time now is 03:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"