+ Reply to Thread
Results 1 to 5 of 5

Count Occurrence Of Text Within Individual Cell And Cell Range

  1. #1
    Registered User
    Join Date
    08-20-2013
    Location
    Schenectady, NY
    MS-Off Ver
    Excel 2011 Mac
    Posts
    64

    Count Occurrence Of Text Within Individual Cell And Cell Range

    Hi. I am trying to create a formula which would count all of the occurrences of a certain text within a cell, as well as within a cell range.

    Ex:

    .....A
    1...aa
    2...a
    3...b
    4...b
    5...c

    In this example, the character "a" occurs 3 times in column A. The formula =COUNTIF(A:A,"*a*") will result in 2 because it cannot count "a" more than one time per cell.

    How can I, within column A, count "a" more than once per cell without specifying the cell (A1)?

  2. #2
    Registered User
    Join Date
    08-20-2013
    Location
    Schenectady, NY
    MS-Off Ver
    Excel 2011 Mac
    Posts
    64

    Re: Count Occurrence Of Text Within Individual Cell And Cell Range

    I have found that the formula =SUM(LEN(A:A)-LEN(SUBSTITUTE(A:A,"a","")))/LEN("a") will work, as long as it is entered as an array (command+return on a Mac).

    Are there other formulas that would also work without needing to be entered as arrays?
    Last edited by jhudson444; 10-06-2013 at 06:07 PM.

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count Occurrence Of Text Within Individual Cell And Cell Range

    You can use the SUMPRODUCT function:

    =SUMPRODUCT(LEN(A2:A10)-LEN(SUBSTITUTE(A2:A10,"a","")))/LEN("a")

    You should avoid using entire columns as range references in the SUMPRODUCT function. Use smaller specific ranges.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    08-20-2013
    Location
    Schenectady, NY
    MS-Off Ver
    Excel 2011 Mac
    Posts
    64

    Re: Count Occurrence Of Text Within Individual Cell And Cell Range

    Thank you!

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count Occurrence Of Text Within Individual Cell And Cell Range

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. sum of a range of values based on count of occurrence of letter in range
    By vsbhogar in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-08-2013, 01:02 PM
  2. [SOLVED] formula to count word occurrence for every nth cell
    By jckmd in forum Excel General
    Replies: 8
    Last Post: 06-03-2012, 07:02 PM
  3. Replies: 2
    Last Post: 03-31-2012, 01:08 AM
  4. Replies: 5
    Last Post: 08-23-2010, 07:05 AM
  5. Count occurrence of character within a cell
    By Kelli in forum Excel General
    Replies: 2
    Last Post: 01-18-2006, 10:25 AM

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