+ Reply to Thread
Results 1 to 3 of 3

Formula to count wild card text in one column that is less than 90 days old

  1. #1
    Registered User
    Join Date
    08-10-2018
    Location
    Minnesota
    MS-Off Ver
    Office 2016
    Posts
    4

    Formula to count wild card text in one column that is less than 90 days old

    I am trying to count the items in one column that may include any words from a list of a few key words. Example includes nice, good, great, or excellent. I am looking for a sum total of any occurrences of these words.

    In addition to these criteria, I only want to count these words if an associated date from the same row in another column is less than 90 days.

    =SUMPRODUCT(--($C$2:$C$50000>TODAY()-90),--ISNUMBER(SEARCH("nice",$F$2:$F$50000)),--ISNUMBER(SEARCH("good",$F$2:$F$50000)),--ISNUMBER(SEARCH("great",$F$2:$F$50000)),--ISNUMBER(SEARCH("excellent",$F$2:$F$50000)))

    If I only use 1 wildcard word, the formula appears to work. If I add multiple words, it does not. It returns zero.
    =SUMPRODUCT(--($C$2:$C$50000>TODAY()-90),--ISNUMBER(SEARCH("good",$F$2:$F$50000))

    I cannot figure out what is wrong with the above formula. Any help would be greatly appreciated!
    Last edited by philalethes; 08-23-2018 at 04:05 PM.

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Formula to count wild card text in one column that is less than 90 days old

    =SUMPRODUCT(--($C$2:$C$50000>TODAY()-90),ISNUMBER(FIND("nice",$F$2:$F$50000))+ISNUMBER(FIND("good",$F$2:$F$50000))+ISNUMBER(FIND("great",$F$2:$F$50000))+ISNUMBER(FIND("excellent",$F$2:$F$50000)))
    or
    =SUMPRODUCT(--($C$2:$C$50000>TODAY()-90),--ISNUMBER(FIND({"nice","good","great","excellent"},$F$2:$F$50000)))

  3. #3
    Registered User
    Join Date
    08-10-2018
    Location
    Minnesota
    MS-Off Ver
    Office 2016
    Posts
    4

    Re: Formula to count wild card text in one column that is less than 90 days old

    Works perfectly! Many 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] Count Win Loss record with differing header titles, (wild card?)
    By JO505 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-25-2013, 04:01 PM
  2. [SOLVED] Fill Column with Workbook Name Using Wild Card
    By Ariff_Chowdhury in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-26-2013, 06:55 PM
  3. unique count beginning with or using zz* wild card
    By bharathsd in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-02-2013, 02:37 AM
  4. Wild card in formula
    By scwx in forum Excel General
    Replies: 7
    Last Post: 03-12-2012, 10:42 PM
  5. If Then formula with wild card
    By zdonner in forum Excel General
    Replies: 1
    Last Post: 01-19-2011, 11:27 AM
  6. Wild Card with Text Values
    By Wkruger in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-22-2010, 10:29 PM
  7. [SOLVED] Wild card in two condition text formula
    By jmrd in forum Excel General
    Replies: 1
    Last Post: 03-03-2006, 02:40 PM
  8. [SOLVED] Is it possible to use a wild card in a =COUNT(IF equation?
    By JDavis in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-28-2005, 09:05 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