Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Countif formula to count number in a range

    I have a column of numbers and need to count how many 1's, 2's, 3's occur. I've tried =countif(a1:a6,"1*") as an array formula but don't get the correct answer.

    135
    2345
    12345
    234
    45
    145

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Try {=SUM(LEN(A1:A6))-SUM(LEN(SUBSTITUTE(A1:A6,"1","")))}
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    WebGenii beat me to the punch...nice job. I keep forgetting about the power of array formulas! Anyway attached is a workbook with the UserDefined function I was working on {just another approach but not as efficient as the array formula} as well as a column using Catherine's solution modified so it is copyable down the rows for each succeeding digit. There is always more than one way to skin that proverbial CAT!
    Attached Files Attached Files
    Last edited by RetiredGeek; 2011-01-26 at 17:59. Reason: To include referenced attachment.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Or for a total
    {=SUM((LEN(A1:A6)-LEN(SUBSTITUTE(A1:A6,TRANSPOSE($b$1:$b$3),""))))}
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    array-enter:
    =COUNT(FIND("1",A1:A6))
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    That will work, but it will only pick up the first instance of the character in the string. I guess it depends on the format of the data.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Dang, busted again by the WebGenii. I'm not going to rub your lamp any more...
    Here is a fixed UDF.
    Attached Images Attached Images
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    True - I confess I made that assumption based on the sample data and the initial reference to COUNTIF.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •