+ Reply to Thread
Results 1 to 3 of 3

Counting cells that matches any of multiple criteria

  1. #1
    Registered User
    Join Date
    10-02-2011
    Location
    Riga
    MS-Off Ver
    Excel 2003
    Posts
    2

    Counting cells that matches any of multiple criteria

    Hi!

    I have following cells of text:

    A3 "square is beautiful"
    A4 "quad is nice"
    A5 "cube is boring"
    A6 "there are many squares"
    A7 "not all quads are equal"
    A8 "my ice cubes are melting"
    A9 "add one sugar cube and two ice cubes"

    I want to show value "1" in column "B" if the cell includes ANY of following words:

    square
    squares
    quad
    quads
    cube
    cubes

    For example in the case of cell A6 ("there are many squares") I type following formula in the cell B6:

    =COUNTIF(A6,"*square*")+COUNTIF(A6,"*quad*")+COUNTIF(A6,"*cube*")

    and I am happy to see value "1" in the cell B6.

    BUT in the case of cell A9 ("add one sugar cube and two ice cubes") the following formula in the cell B9:

    =COUNTIF(A9,"*square*")+COUNTIF(A9,"*quad*")+COUNTIF(A9,"*cube*")

    shows value "2" in the cell B9.

    What to do to show value "1" in the cell B9 in this case?


    JeePee

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

    Re: Counting cells that matches any of multiple criteria

    Hello JeePee, I moved your thread to the "general" forum....

    You can amend like this

    =(COUNTIF(A6,"*square*")+COUNTIF(A6,"*quad*")+COUNTIF(A6,"*cube*")>0)+0

    or shorter

    =(SUM(COUNTIF(A6,"*"&{"square","quad","cube"}&"*"))>0)+0
    Audere est facere

  3. #3
    Registered User
    Join Date
    10-02-2011
    Location
    Riga
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Counting cells that matches any of multiple criteria

    Thanks a lot!

+ 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