+ Reply to Thread
Results 1 to 9 of 9

Countif cell contains text OR other text

  1. #1
    Registered User
    Join Date
    12-04-2013
    Location
    nashville, tn
    MS-Off Ver
    Excel 2010
    Posts
    23

    Countif cell contains text OR other text

    I am trying to generate a count if a particular cell contains the text "*Microsoft Word*" OR "*.doc*". I am using this formula in the attached spreadsheet at M2:

    =COUNTIF(E2,"*Microsoft Word*")+COUNTIF(E2,"*.doc*")

    The problem is that when I use that formula, it returns a value of 2. I only want it to count once if it sees either of those parameters ("Microsoft Word" or ".doc"), not both. Therefore the value returned should be 1.

    Any help would be greatly appreciated!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Countif cell contains text OR other text

    This should do it:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Please click the * icon below if I have helped.

  3. #3
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Countif cell contains text OR other text

    If you want a '1' instead of 'True', try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Regards,
    Aardigspook

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Countif cell contains text OR other text

    One way...

    =--(COUNT(SEARCH({"Microsoft Word",".doc"},E2))>0)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Countif cell contains text OR other text

    Another way:
    =COUNTIF(E2;"*Microsoft Word*.doc*")
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Countif cell contains text OR other text

    I don't think that works.

    The cell would have to contain both substrings to get counted.

  7. #7
    Registered User
    Join Date
    12-04-2013
    Location
    nashville, tn
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Countif cell contains text OR other text

    A TRUE - FALSE will work as well. Thanks!

  8. #8
    Registered User
    Join Date
    12-04-2013
    Location
    nashville, tn
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Countif cell contains text OR other text

    Perfect! Thanks!

  9. #9
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Countif cell contains text OR other text

    You're welcome and thanks for the rep.

    Regards,
    Aardigspook

    If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
    You don't need to give me rep if I helped, but thanks are appreciated.

+ 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] COUNTIF(S) multiple criteria (text & text)
    By jholiday78 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-03-2014, 08:18 PM
  2. [SOLVED] Count # of times text appears in cell range by cell referencing not =countif(A:A, ="text")
    By smit.etha in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-20-2014, 04:09 AM
  3. Using COUNTIF with text from another cell
    By Blenderr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-07-2014, 10:54 AM
  4. [SOLVED] PLEASE HELP: COUNTIF text is in the cell
    By sharmane3 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-24-2014, 03:32 PM
  5. Using cell data as partial text search with COUNTIF
    By CarV in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-08-2014, 12:29 PM
  6. [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
  7. COUNTIF text where there is more than one word in a cell
    By Abi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-20-2005, 07:10 AM

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