+ Reply to Thread
Results 1 to 2 of 2

Countifs with a "findlist" to find items in one list, but exclude items in "exclusions"

Hybrid View

  1. #1
    Registered User
    Join Date
    02-05-2019
    Location
    Chicago
    MS-Off Ver
    2007
    Posts
    1

    Countifs with a "findlist" to find items in one list, but exclude items in "exclusions"

    New to COUNTIFS
    I'm using =SUMPRODUCT(COUNTIF(B18,"*"& FINDLIST &"*"))>0
    To give me a simple true / false if an item in column B includes text defined in my named range findlist

    Some of the items in findlist include "teak" "ash" "wood". (looking for commonly used wood materials)
    Because data is non-standardized coming in, listing all variants would be onerous ("teakroot" "teak root" "FSCTeak" - I want to test true, but to anticipate and create all of these is a large job and could lead to errors).

    So here's the rub: of course, on the flip side, there will be false trues, as far as my needs are concerned ("steak knife", "washers") will test true, and I will need to ignore these items for next steps.

    I'm wondering if I can create a list of exclusions that can be developed as I find the common exceptions. If that could even be done (with countifs?), what would the result be, if there were items on both the include and exclude list (Example: "Rubberwood washers" would include two items from my find list "wood" "ash", and one from my excludelist "washers". Would this evaluate as true, as I would hope? And would "steel washer" evaluate to false, as the include / excludes would cancel out?

    Thanks!

  2. #2
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Countifs with a "findlist" to find items in one list, but exclude items in "exclusions

    Looking at your samples, all of your 'false trues' appear to be in the middle of words, would this be a fair assesment of reaility, or could you have something like FSCTeakroot as a true match as well?

    This should count words that begin or end with a word in the FINDLIST, such as "teakroot" "teak root" and "FSCTeak" but not those that contain a word from FINDLIST in the middle, like "steak knife", "washers" or "FSCTeakroot"

    =(SUMPRODUCT(COUNTIF(" "&B18,"* "& FINDLIST &"*"))+SUMPRODUCT(COUNTIF(B18&" ","*"& FINDLIST &" *")))>0

    Think the formula is right, but haven't tested it.

+ 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. COUNTIFS to show "open" line items in spreadshetet
    By awaters7832 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-27-2017, 03:47 PM
  2. Replies: 2
    Last Post: 07-24-2017, 02:19 AM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. "Find" macro to seach a list of defined items from one worksheet to another.
    By Mike Steinhauer in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-26-2015, 05:13 PM
  5. Moving 30 days and older messages from "Sent Items" to "Deleted Items"
    By bandera in forum Outlook Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2014, 07:09 AM
  6. Replies: 1
    Last Post: 07-16-2010, 02:44 AM
  7. Replies: 3
    Last Post: 08-27-2009, 07:26 AM

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