+ Reply to Thread
Results 1 to 10 of 10

Using COUNTIF to match dates

  1. 01-31-2016, 05:10 AM #1
    chilli76
    chilli76 is offline
    Forum Contributor
    Join Date
    11-24-2013
    Location
    Huddersfield, England
    MS-Off Ver
    2013
    Posts
    104

    Using COUNTIF to match dates

    Hi guys

    I am trying to breakdown some data into months for analysis but I can't get the COUNTIF function to match the date format. I've attached an example sheet and I'm after Cells in AS7:18 to populate from matching cells in A:A. I've tried changing the formats and the only way I could get it to work was to physically type in the date in text format but that's not really what I'm after as I copy and paste the data into this sheet from another.

    Any help much appreciated

    Jonathan
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-31-2016, 05:22 AM #2
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Using COUNTIF to match dates

    Use this in AS7 and copy down-
    Please Login or Register  to view this content.
    Happy to Help

    How to upload excel workbooks at this forum - http://www.excelforum.com/the-water-...his-forum.html

    "I don't get things easily, so please be precise and elaborate"

    If someone's post has helped you, thank by clicking on "Add Reputation" below the post.
    If your query is resolved please mark the thread as "Solved" from the "Thread Tools" above.

    Sourabh
    Reply With QuoteRegister To Reply
  3. 01-31-2016, 05:35 AM #3
    chilli76
    chilli76 is offline
    Forum Contributor
    Join Date
    11-24-2013
    Location
    Huddersfield, England
    MS-Off Ver
    2013
    Posts
    104

    Re: Using COUNTIF to match dates

    works a treat, many thanks
    Reply With QuoteRegister To Reply
  4. 01-31-2016, 05:49 AM #4
    chilli76
    chilli76 is offline
    Forum Contributor
    Join Date
    11-24-2013
    Location
    Huddersfield, England
    MS-Off Ver
    2013
    Posts
    104

    Re: Using COUNTIF to match dates

    Hi again

    Unfortunately my other analysis cells don't work though. AU, AX & AY. They use sumproduct but won't reference column A. Any ideas?

    Cheers
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 01-31-2016, 06:03 AM #5
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Using COUNTIF to match dates

    You are matching the dates, however you need to match the month and the year. So you need to use month and year functions.
    In AU7 use this-
    Please Login or Register  to view this content.
    In AX7-
    Please Login or Register  to view this content.
    In AY7-
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 01-31-2016, 06:31 AM #6
    chilli76
    chilli76 is offline
    Forum Contributor
    Join Date
    11-24-2013
    Location
    Huddersfield, England
    MS-Off Ver
    2013
    Posts
    104

    Re: Using COUNTIF to match dates

    That is great, thanks very much

    Jonathan
    Reply With QuoteRegister To Reply
  7. 01-31-2016, 06:39 AM #7
    chilli76
    chilli76 is offline
    Forum Contributor
    Join Date
    11-24-2013
    Location
    Huddersfield, England
    MS-Off Ver
    2013
    Posts
    104

    Re: Using COUNTIF to match dates

    One last query if I may

    It works fine but it's missing some, for example there are 11 selections in December but it's only recording 9. Any ideas?

    Thanks
    Reply With QuoteRegister To Reply
  8. 01-31-2016, 06:48 AM #8
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Using COUNTIF to match dates

    Missed to fix the arrays-
    Use this- and copy down
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 01-31-2016, 06:55 AM #9
    chilli76
    chilli76 is offline
    Forum Contributor
    Join Date
    11-24-2013
    Location
    Huddersfield, England
    MS-Off Ver
    2013
    Posts
    104

    Re: Using COUNTIF to match dates

    Very kind of you my friend. All working great now.

    Jonathan
    Reply With QuoteRegister To Reply
  10. 01-31-2016, 07:28 AM #10
    sourabhg98
    sourabhg98 is offline
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Using COUNTIF to match dates

    Glad to know that worked!!

    You can click on "Add Reputation" below my post if that helped!
    Also if your query is resolved please mark this thread as "Solved" from the "Thread Tools" at the top of this page.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Countif value in cell is found in another range whilst two sets of dates also match..
    By Barking_Mad in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-20-2015, 06:44 AM
  2. Copy data to another file based on dates when the dates in the files do not match
    By westwd in forum Excel General
    Replies: 1
    Last Post: 12-17-2014, 08:14 PM
  3. Return dates that match a single criteria (a countif criteria)
    By nickmax1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-07-2014, 12:43 PM
  4. [SOLVED] Highlight Group of Dates if Dates Match List of other Dates
    By martinpgibson in forum Excel General
    Replies: 5
    Last Post: 10-24-2012, 08:14 PM
  5. Match, Offset, Match, Index, Countif = VLOOKUP Handles Duplicates
    By dim06 in forum Excel General
    Replies: 2
    Last Post: 03-16-2012, 12:03 PM
  6. Excel 2007 : Countif certain dates
    By Hikertrash in forum Excel General
    Replies: 3
    Last Post: 08-15-2011, 03:21 PM
  7. COUNTIF function with Dates & due dates.
    By kcowper in forum Excel General
    Replies: 3
    Last Post: 03-10-2011, 12:51 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1