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

1. ## 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. ## 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. ## Re: find number of times a letter or a number appears in a column

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

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

#### 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