+ Reply to Thread
Results 1 to 4 of 4

Count occurrences of multiple values in one cell (50+ values / all special characters)

  1. #1
    Registered User
    Join Date
    05-04-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Count occurrences of multiple values in one cell (50+ values / all special characters)

    You've probably received an email with a heart, star or arrow in the subject line. For my job, I have a data set of ~6-10k emails per month and I calculate various stats. One stat I use is the number of these characters are in subject lines. To get this, I have been using...

    =LEN(SubjectLineA)-LEN(SUBSTITUTE(SubjectLineA,"Character1",""))+LEN(SubjectLineA)-LEN(SUBSTITUTE(SubjectLineA,"Character2",""))+...... (repeated for the 50+ characters)

    Result: SubjectLineA had 4 special characters (of the 50+)

    but I've reached some limit with this approach where Excel (2010 and 2013 w/12GB ram) locks up when I modify the formula or add additional characters. It's not a calculation issue.

    I have been unable to find another way to tackle this. Any ideas?

    -J

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count occurrences of multiple values in one cell (50+ values / all special characters)

    Hi,

    You can perform and sum the searches together. For example, with your list of special characters in D1:D50:

    =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,D1:D50,"")))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    05-04-2012
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Count occurrences of multiple values in one cell (50+ values / all special characters)

    Thanks XOR LX. Very straight forward. I'll find several uses for it. Much appreciated!

    -J

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count occurrences of multiple values in one cell (50+ values / all special characters)

    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. How to SUM values even special characters?
    By toplisek in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-01-2013, 06:36 AM
  2. [SOLVED] In a single cell, count any occurrences from a list of values
    By daedelous00 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-05-2013, 03:30 PM
  3. How to calculate only values with numbers not special characters like!
    By toplisek in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-04-2013, 06:43 AM
  4. Need a count of unique values in col. with multiple values in a cell.
    By DrewS in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-22-2013, 01:03 PM
  5. Count occurrences of values in a column??!!
    By me123 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-10-2006, 03:40 PM

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