+ Reply to Thread
Results 1 to 3 of 3

Using cell data as partial text search with COUNTIF

  1. #1
    Registered User
    Join Date
    02-08-2014
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    2

    Using cell data as partial text search with COUNTIF

    Usually, when you want to count the amount of cells containing a text you can use wildcards.
    Like =COUNTIF(A:A,"*NAB*").

    I want to count a lot of different things, so I have all what needs to be searched for in a column.
    Usually, you use =COUNTIF(A:A,B1).

    However, I need a combination of both. Something like =COUNTIF(A:A,"*"B1"*").
    Is there any function which allows for such constructions? Or how should a workaround be made?

    It's very important to note I'm looking for text in cells that contain more text than only the text I'm looking for (therefore the wildcards).
    Preferably, the source text is in one column, the text which needs to be looked for in a second column and the output of the function in a third column (seperate output per lookup text).

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

    Re: Using cell data as partial text search with COUNTIF

    You need to use & to concatenate, i.e.

    =COUNTIF(A:A,"*"&B1&"*")
    Audere est facere

  3. #3
    Registered User
    Join Date
    02-08-2014
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Using cell data as partial text search with COUNTIF

    Quote Originally Posted by daddylonglegs View Post
    =COUNTIF(A:A,"*"&B1&"*")
    Works like a charm, thanks!

+ 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. [SOLVED] How to countif to partial text
    By ks100 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-19-2013, 11:42 AM
  2. [SOLVED] Countif to search for text in a cell
    By holycowbanana85 in forum Excel General
    Replies: 3
    Last Post: 07-26-2012, 03:56 AM
  3. [SOLVED] PARTIAL TEXT Search from range of cells.
    By manharji in forum Excel General
    Replies: 1
    Last Post: 04-07-2012, 08:19 AM
  4. Search for Partial Text
    By grkchakri in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-18-2012, 06:47 AM
  5. COUNTIF on partial cell data
    By FinchC in forum Excel General
    Replies: 8
    Last Post: 01-27-2011, 10:10 PM

Tags for this Thread

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