Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count instances of a letter in a range of cells (2003)

    I have a spreadsheet with cells that have any combination of letters in them.

    I need a way to determine how many times a given letter appears in a given range.

    For example, A1 may have "ABCD" and B1 may have "ABD"

    I need to know that A, B, & D appear twice but C only appears once.
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count instances of a letter in a range of cells (2003)

    See the attached sample workbook. It uses an array formula (confirmed with Ctrl+Shift+Enter) with the SUM, LEN and SUBSTITUTE functions.

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Count instances of a letter in a range of cell

    I'm curious, can you give me an idea of the general scenario that requires this solution?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count instances of a letter in a range of cell

    My children have several paper delivery routes. Each pays a set amount. Sometimes 2 of them do each route. Sometimes there is a friend that helps. Sometimes the little sisters help.

    My wife wanted a way to automatically split the $$ for each route among those that help.

    if less than 4 people do a route, they split the entire amount. If more than 3 do it, each one gets $1 and Mom & Dad get the rest (we will drive them if there are more than 3)

    The solution was to put their initial in a cell and then based on the number of initials, that's how the $$ got split.

    For example, My kids initials are T, J, B. If they all help on one route, then the cell will say JTB. Since that route pays $15, they each get $5. If a friend helps, it would say TJBE and they would each get $1.

    I am trying to find out:
    *How many routes each person did that week
    *How much they earned for that week..

    The answer to the original question may help me determine how many they did.

    The real kicker here is this: My wife wants to make this spreadsheet herself. She is NOT a computer user so this is her way of learning. I offered to help for the hard parts, but based on her design choice, I have to calculate pay the hard way.
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

Posting Permissions

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