+ Reply to Thread
Results 1 to 2 of 2

How to use the countif funtion to count a codes that contain cells with muliple codes.

  1. #1
    Registered User
    Join Date
    12-09-2013
    Location
    Clifton Heights, PA
    MS-Off Ver
    Excel 2010
    Posts
    1

    Unhappy How to use the countif funtion to count a codes that contain cells with muliple codes.

    I have an excel spreadsheet with a column that contains muliple codes separated by ~. (IE. ~33~)

    For instance,
    Column A CONTAINS A SERIES OF CODES USED FOR IDENTIFYING TYPES OF DATA
    A1= ~1~~22~~45~
    A2= ~33~~133~~22~
    A3= ~33~
    A4= ~133~
    A5= ~126~

    Column B contains the average days pending
    B1= 24
    B2=25
    B3=48
    B4=37
    B5=22

    Does anyone know what function I can use to count how many times "33" appears in column A? When I use =COUNTIF(A:A,"~33~"), it only counts 1. When I use =COUNTIF(A:A,"*33*"), it counts 4.

    Please help

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: How to use the countif funtion to count a codes that contain cells with muliple codes.

    welcome to the forum. so it's supposed to return 2? try:
    =COUNTIF(A:A,"*~~33~~*")

    when you use tilde (~), it take the next symbol literally. that means if A1:A2 contains:
    *A
    BA

    and i want to count those *A, i cannot use:
    =COUNTIF(A:A,"*A")
    that will be anything before "A".

    i must use it with a tilde to take the asterisk literally.
    =COUNTIF(A:A,"~*A")

    if it's possible 1 single cell contains 2 "~33~", and you want it as 2 counts, then:
    =SUMPRODUCT(--(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"~33~",""))))/LEN("~33~")

    the last portion can of course be divided by 4 instead of LEN("~33~"). just in case you need it for other characters

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

+ 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. Replies: 0
    Last Post: 10-09-2013, 07:59 PM
  2. Combining vba codes makes the previous codes broken !
    By MDPLUS in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-26-2013, 10:00 AM
  3. [SOLVED] need formula to count number of cells that contain any of a LARGE list of zip codes
    By Security in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-14-2012, 10:37 AM
  4. Replies: 6
    Last Post: 11-28-2006, 01:08 PM
  5. How do I count alpha codes in a range of cells in a row?
    By Barbarosa in forum Excel General
    Replies: 1
    Last Post: 02-07-2006, 12:20 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