+ Reply to Thread
Results 1 to 3 of 3

Use Countif and ?? to count occurrences of text string in a column

  1. #1
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    315

    Use Countif and ?? to count occurrences of text string in a column

    Hello,

    I am wondering if it is possible (without using a helper column) to match data in a string and then count the number of occurrences. I have a column of text strings (over 3K rows right now) and I want to count how many times 19 occurs in the 3rd and 4th position of the string. I can add a helper column where I pull out the 3rd and 4th characters and then count the occurrences then but this sheet will increase in size each week by about 200 rows and I don't want to have to copy/paste the MID() function every week in a helper column.

    Thank you!

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Use Countif and ?? to count occurrences of text string in a column

    If these are text strings (not numbers) you can use wildcards like this

    =COUNTIF(A:A,"??19*")

    For text or numbers use SUMPRODUCT, e.g.

    =SUMPRODUCT(--(MID(A2:A4000,3,2)="19"))
    Audere est facere

  3. #3
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    315

    Re: Use Countif and ?? to count occurrences of text string in a column

    SUMPRODUCT worked perfectly! Thank you very much!!!!!

+ 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: 07-28-2013, 10:24 AM
  2. [SOLVED] Count occurrences of a text string in a range of cells
    By skysurfer in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-30-2012, 04:01 PM
  3. Formula to Count the Number of Occurrences of a Text String in a Range
    By poug1903 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-10-2011, 06:49 AM
  4. Count the occurrences of a text string?
    By New2MSExcel in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-24-2010, 08:47 PM
  5. Replies: 1
    Last Post: 09-18-2009, 04:15 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