+ Reply to Thread
Results 1 to 3 of 3

find number of times a letter or a number appears in a column

  1. #1
    Registered User
    Join Date
    11-06-2003
    MS-Off Ver
    2013
    Posts
    75

    find number of times a letter or a number appears in a column

    I have letters and numbers in cells and want to count the number of times that a particular letter or number appears in the column of cells.
    Example: I know that there are 9 C's in the list below. I want to count the number of times that the number 1 shows up in the list, or the number 2, etc. What formula would work best for this function?

    C1-02
    C1-03
    C1-04
    C1-05
    C1-06
    C2-08
    C2-07
    C2-14
    C1-09

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,854

    Re: find number of times a letter or a number appears in a column

    Hi Dcoates,

    If you want a count of how many cells contain the letter "C", you could use:

    =COUNTIF(A1:A9,"*c*")

    However, this will only count each cell once. If a cell has three C's in it, the total will only reflect one. If you need to count all instances of a letter or number, the following array formula might help (confirm using CTRL+SHIFT+ENTER, not just ENTER):

    =SUM(LEN(A1:A9)-LEN(SUBSTITUTE(A1:A9,"C","")))

    Note that this formula is case sensitive, while the first is not.

  3. #3
    Registered User
    Join Date
    11-06-2003
    MS-Off Ver
    2013
    Posts
    75

    Re: find number of times a letter or a number appears in a column

    Fantastic! That saved so much time for me. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1