+ Reply to Thread
Results 1 to 6 of 6

Countif for dates with wildcards

  1. #1
    Registered User
    Join Date
    05-12-2016
    Location
    Newcastle, England
    MS-Off Ver
    2010
    Posts
    10

    Countif for dates with wildcards

    Hi All,

    Looking for a bit of help please, i have a list of issues/complaints from January 2017 to date. They are currently just a list in a spreadsheet i want to count how many occur in each month.

    The date format is 03/01/2017 09:00:00, (only 03/01/2017 is visibale in the cell, its only when you click on a specific example that you see the time is also included) I've tried doing

    =COUNTIF(Complaints!C:C,"*/01/2017*") but this didn't work,it returns a zero and i know this is incorrect. I've been reading online and some similar questions have been advised to use the sumproduct function but this seems to be in more complex questions then mine - i tried the below but the #Value error is returned.

    =SUMPRODUCT(MONTH(Complaints!C:C)=1)

    What am i missing?

    Any pointers much appreciated

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,095

    Re: Countif for dates with wildcards

    How about try...

    =SUMPRODUCT(--(C1:C50<>""),--(MONTH(C1:C50)=1))

    I wouldn't use the whole range with sumproduct as it will evaluate all cells in the range which makes it inefficient.
    HTH
    Regards, Jeff

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,726

    Re: Countif for dates with wildcards

    Upload sample file please. Without looking at actual spreadsheet, it's impossible to tell what's actually stored in a cell.

    It could be either DateTime value (with underlying decimal/double value) or string/text stored in cell.

    Provided that cells actually store datetime values. You can use COUNTIFS to count values in a given month.
    =COUNTIFS(Complaints!C:C,">="&DATE(2017,1,1),Complaints!C:C,"<"&DATE(2017,2,1))

    As for SUMPRODUCT... try.
    =SUMPRODUCT((MONTH(Complaints!C:C)=1)*1)

    However, you should take care to use only the range containing values for SUMPRODUCT. Unlike SUMIF(s),COUNTIF(s) family of function, SUMPRODUCT will iterate over entire range specified, and create unnecessary overhead. (Ex. Use C1:C200 if data is in that range, instead of entire column C:C).
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  4. #4
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Countif for dates with wildcards

    How depends on if the dates are dates or text. From your post it sounds like they are dates.

    If thats the case, be aware that dates arent something like "03/01/2017" to Excel. All date and time values in Excel are actually serial numbers representing the date in a decimal number representation with whole numbers representing the day, month year as the number of days from Jan 1, 1900 and the decimal portion representing the time of day 0= 12 am, .99 (repeating) = 11:59:59pm.

    So you could figure out the whole number portion of the serial number and use that as a criteria.

    However, it is likely easier to use SUMPRODUCT with the MONTHS function as CK76 shows. Id further recommend only referencing the data range you are using instead of the full column (ex: C2:C10 instead of C:C).

  5. #5
    Registered User
    Join Date
    05-12-2016
    Location
    Newcastle, England
    MS-Off Ver
    2010
    Posts
    10

    Re: Countif for dates with wildcards

    Thanks

    =COUNTIFS(Complaints!C:C,">="&DATE(2017,1,1),Complaints!C:C,"<"&DATE(2017,2,1))

    seems to be doing the trick - much appreciated

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,095

    Re: Countif for dates with wildcards

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. 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. Ignoring Wildcards in Countif
    By hullmo in forum Excel General
    Replies: 5
    Last Post: 05-11-2021, 02:41 AM
  2. [SOLVED] Using Wildcards in CountIf
    By SilverBullet17 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-16-2017, 06:21 AM
  3. CountIF and Wildcards
    By Justmegan93 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-09-2014, 09:05 AM
  4. [SOLVED] COUNTIF Using Wildcards
    By JoeJaycee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-24-2012, 02:52 PM
  5. Countif with wildcards
    By seanjoe in forum Excel General
    Replies: 4
    Last Post: 06-02-2010, 09:04 AM
  6. is it possible to use countif with a cell ref and wildcards?
    By videot96 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-25-2009, 05:10 PM
  7. countif wildcards?
    By Ltat42a in forum Excel General
    Replies: 3
    Last Post: 12-18-2005, 12:59 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