+ Reply to Thread
Results 1 to 4 of 4

Partial String Match in COUTIF Criteria Range?

  1. #1
    Registered User
    Join Date
    06-21-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Partial String Match in COUTIF Criteria Range?

    Hello all,

    I am trying to return the count a certain string in one column (A:A).

    The only criteria is that column (A:A) must match the contents of whatever is in column B. Let's say we're looking at row 2:

    So:

    =COUNTIF(SHEET!A:A,B2)

    This works fine, obviously, but what if I wanted to match B2 with anything in (A:A) that started with the contents of B2, even if it has additional text after it?

    For example, I want to count instances of cells that start with 'BLUE', but in A:A we find the following values: 'BLUEcar', 'BLUEhouse', 'BLUEman'

    I can't alter the data in column A and additionally, I won't be able to add an additional column that does =LEFT(A2,3) and then run the COUNTIF on that. Basically, if all has to be done within one formula.

    Thank you.

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

    Re: Partial String Match in COUTIF Criteria Range?

    Try using a "wildcard"

    =COUNTIF(SHEET!A:A,B2&"*")
    Audere est facere

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,842

    Re: Partial String Match in COUTIF Criteria Range?

    You could try:

    =COUNTIF(SHEET!A:A,B2&"*")

    - Moo

    ... DL beat me to it

  4. #4
    Registered User
    Join Date
    06-21-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Partial String Match in COUTIF Criteria Range?

    Wow, I completely forgot about wildcards.

    Thanks so much, guys.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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