+ Reply to Thread
Results 1 to 8 of 8

Wildcard not working in my COUNTIFS

  1. #1
    Registered User
    Join Date
    05-25-2011
    Location
    California, USA
    MS-Off Ver
    Office 2016 on Mac and Windows
    Posts
    57

    Question Wildcard not working in my COUNTIFS

    I'm trying to count the number of cells in a column matching a couple different sets of criterion. Column B has the first match up, purely based on text. Column F is a date/time in the format #/##/#### ##:##". Example: 7/1/2014 0:00. Consistently in the data, single digit months and days are represented by one character instead of two (e.g. April 7th is 4/7/2014 instead of 04/07/2014).

    I don't care about the day in the comparison. I need monthly totals -- so all with any date in July 2014, for example. I also do not care about the time.

    My formula:

    =COUNTIFS('MG-Kinders'!$B:$B,"=Almond",'MG-Kinders'!$F:$F,"9/1/2014 0:00")

    ...correctly returns the number 17--17 cells that match "Almond" in column B and all September 1st midnight dates in column F. I am trying now to use wildcards to test for any date and any time in September 2014. So I tried the following formula:

    =COUNTIFS('MG-Kinders'!$B:$B,"=Almond",'MG-Kinders'!$F:$F,"9/??/2014*")

    ...which incorrectly returned 0. It should at least return those 17 if I were doing this right. So then I tried these variations, all to the same 0 result:

    =COUNTIFS('MG-Kinders'!$B:$B,"=Almond",'MG-Kinders'!$F:$F,"9/*/2014*")
    =COUNTIFS('MG-Kinders'!$B:$B,"=Almond",'MG-Kinders'!$F:$F,"9/*/2014 ??:??")
    =COUNTIFS('MG-Kinders'!$B:$B,"=Almond",'MG-Kinders'!$F:$F,"9/??/2014 ??:??")

    Can anyone help me out here? What am I doing wrong with this formula? How do I use the wildcards correctly?

    dt

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

    Re: Wildcard not working in my COUNTIFS

    Wildcards only work with text, dates are numbers so you can't use them in that way - if you want to include all of September 2014 then you need a start and end date, e.g.

    =COUNTIFS('MG-Kinders'!$B:$B,"Almond",'MG-Kinders'!$F:$F,">="&DATE(2014,9,1),'MG-Kinders'!$F:$F,"<"&DATE(2014,10,1))
    Audere est facere

  3. #3
    Registered User
    Join Date
    05-25-2011
    Location
    California, USA
    MS-Off Ver
    Office 2016 on Mac and Windows
    Posts
    57

    Re: Wildcard not working in my COUNTIFS

    Awesome! I didn't realize that at all--thanks, this solved it. It appears the date range tested though is inclusive, and so I would need 10/1 to fall outside the range 9/1-9/30 though, so I just changed the 10/1 to the last day of September.

    Thanks very much.

    dt

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

    Re: Wildcard not working in my COUNTIFS

    Quote Originally Posted by dtrimble View Post
    It appears the date range tested though is inclusive, and so I would need 10/1 to fall outside the range 9/1-9/30 though, so I just changed the 10/1 to the last day of September.
    The version I suggested won't include 1st October. If you change to 30th September you need to change < to <=...and even then because your data contains time values that could exclude some 30th September data (or are all the times 00:00?)

    My version works in all circumstances so I would stick with that. If you have multiple months to sum it's also easier to use cell references for the dates, e.g. put the 1st of the month in a cell like K2 and use this version

    =COUNTIFS('MG-Kinders'!$B:$B,"Almond",'MG-Kinders'!$F:$F,">="&K2,'MG-Kinders'!$F:$F,"<"&EOMONTH(K2,0)+1)

  5. #5
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    244

    Re: Wildcard not working in my COUNTIFS

    The way the COUNTIFS formula is written is capturing the data that is greater or equal than 9/1/2014 and less than 10/1/14 (9/30/14). One reason to use "less than" the first day of the month is so you don't have to think about how many days are in each month.

  6. #6
    Registered User
    Join Date
    05-25-2011
    Location
    California, USA
    MS-Off Ver
    Office 2016 on Mac and Windows
    Posts
    57

    Re: Wildcard not working in my COUNTIFS

    Quote Originally Posted by CWatsonJr View Post
    The way the COUNTIFS formula is written is capturing the data that is greater or equal than 9/1/2014 and less than 10/1/14 (9/30/14). One reason to use "less than" the first day of the month is so you don't have to think about how many days are in each month.
    DOH!!!! Missed the < entirely. Thanks for pointing that out. That makes sense now.

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

    Re: Wildcard not working in my COUNTIFS

    Quote Originally Posted by daddylonglegs View Post
    Wildcards only work with text
    Yeah, that's what I used to think, also.


    Data Range
    A
    B
    C
    D
    2
    123
    ---
    4
    =SUMPRODUCT(--ISNUMBER(SEARCH("?2?",A2:A10)))
    3
    456
    4
    222
    5
    326
    6
    606
    7
    789
    8
    111
    9
    457
    10
    924


    The SEARCH function is the only function that I know of that will evaluate the wildcards with numeric numbers.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

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

    Re: Wildcard not working in my COUNTIFS

    Hey Tony!

    I don't think I've ever tried that, I stand corrected - not much use for wildcards in SEARCH, I think. Good to know, though, just in case......

+ 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] countifs working on extended ranges and different sheets not working
    By etaf in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-18-2013, 02:23 PM
  2. LIKE not working with wildcard in MS Query
    By RollerRagerMD in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-03-2013, 10:02 AM
  3. [SOLVED] Countifs with dynamic criteria and wildcard characters
    By aamehta24 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-16-2013, 08:39 PM
  4. Copy files via wildcard, FileCopy not working
    By koltregaskes in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-22-2011, 10:33 AM
  5. [SOLVED] Wildcard Not Working in IF statement
    By jrmccaleb@comcast.net in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-09-2005, 04:08 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