+ Reply to Thread
Results 1 to 6 of 6

Count "A" and "V" as separate characters in a single cell

  1. #1
    Registered User
    Join Date
    02-04-2013
    Location
    Florida
    MS-Off Ver
    Excel 2000
    Posts
    2

    Count "A" and "V" as separate characters in a single cell

    I have a chart that tracks visitations and assessments. Currently, I will put an "A" or "V" in the cell, and the spreadsheet will count how many "A"s and how many "V"s are completed each month using the formulas =COUNTIF(C4:C142,"A") OR =COUNTIF(C4:C142,"V").

    However, sometimes a visit AND assessment occur each month, requiring me to put a combination like VA, AVV or VVAV in a single cell. I want the spreadsheet to still be able to count how many "V"s and "A"s occur each month even if this sort of a combination existed in a single cell. Can anyone provide me with an answer?

    Thanks!
    Last edited by sportboy712; 02-04-2013 at 12:27 PM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Count "A" and "V" as separate characters in a single cell

    What's in the cell if there has been no visitation or assesment?

    If it's a blank try this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    wales
    MS-Off Ver
    Excel 2007
    Posts
    964

    Re: Count "A" and "V" as separate characters in a single cell

    for V you can use this. (this allowes for 100 vists per month.)

    =SUMPRODUCT(--(MID(A1,ROW(A1:A100),1)="v"))

    do similar for A

    that whould count everything you need
    The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
    <--- If a post helps hit the star

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Count "A" and "V" as separate characters in a single cell

    Try something like this:
    =SUMPRODUCT(LEN(A1:A4)-LEN(SUBSTITUTE(A1:A4,"A","")))
    Replace "A" with "V" to count V
    Quang PT

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Count "A" and "V" as separate characters in a single cell

    Try this array formula (use CNTRL SHFT ENTER instead of ENTER)

    =SUM(LEN($C$4:$C$142)-LEN(SUBSTITUTE($C$4:$C$142,"V","")))
    If done properly, you'll see brackets around formula {}
    Did that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Registered User
    Join Date
    02-04-2013
    Location
    Florida
    MS-Off Ver
    Excel 2000
    Posts
    2

    Re: Count "A" and "V" as separate characters in a single cell

    Thanks everyone for the quick reply. bebo's formula worked for me the first try!

+ 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