+ Reply to Thread
Results 1 to 10 of 10

Countif based on date range

  1. #1
    Registered User
    Join Date
    12-12-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    92

    Countif based on date range

    Hi,

    I created a spreadsheet that gathers data from other spreadsheets. The other spreadsheets are rolling throughout the year and I would like counts for each specific month. Each date is set up as 12/27/2014, 0:00:01 AM. Currently I delete the past months to get a count of each item needed using
    Please Login or Register  to view this content.
    . Is there code I can add to this to count from 12/1/2014, 0:00:01 AM - 12/31/2014, 12:59:59 PM?

    Thank you for your help.

    I am able to change the spreadsheet if needed. I found some info on VLookup, but didn't understand how to make it work for me.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,000

    Re: Countif based on date range

    You can use COUNTIFS rather than COUNTIF and include further arguments for a start and end date.
    Or you could use a Pivot Table.

    Post a sample workbook if you need more help than that.

    BSB

  3. #3
    Registered User
    Join Date
    12-12-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Countif based on date range

    Thank you for the reply. I am unable to post a sample work book at this time. I posted the jist of the spreadsheet below. I tried a pivot table; however it does not gather accurate counts as some fields have two options.
    12/26/2014, 9:43:54 AM A
    12/26/2014, 9:42:07 AM B
    12/26/2014, 9:15:21 AM C, A
    12/26/2014, 9:05:59 AM A
    12/26/2014, 9:02:19 AM B
    12/26/2014, 8:56:56 AM C, B
    12/26/2014, 8:42:48 AM A
    12/26/2014, 8:37:30 AM B, A
    12/26/2014, 8:33:29 AM C
    12/26/2014, 8:29:01 AM A
    12/26/2014, 8:24:21 AM B, C
    12/26/2014, 8:04:45 AM C
    12/26/2014, 7:58:43 AM A

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,000

    Re: Countif based on date range

    Have a look at the attached. Is this the sort of thing you're trying to do?

    There are probably far more elegant solutions but I'm too tired to think of them...

    BSB.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-12-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Countif based on date range

    For some reason when I enter the code I only receive 0's. It will not calculate correctly. I coppied exactly what you have on the sample spreadsheet and still only 0's. Thoughts?

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,000

    Re: Countif based on date range

    Are your dates and times actually dates and times or formatted as text?

  7. #7
    Registered User
    Join Date
    12-12-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Countif based on date range

    They are formated General

  8. #8
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,000

    Re: Countif based on date range

    For the suggested approach to work they will need to be dates/times.

    You could try reformatting them as "MM/DD/YYYY, HH:MM:SS am/pm" and see if that helps, but not sure it will so a helper column may be required to make things easy.

  9. #9
    Registered User
    Join Date
    12-12-2013
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010
    Posts
    92

    Re: Countif based on date range

    I was able to get it to work with a helper column dictating the month based on the date. Thank you for the formula and the help.

  10. #10
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    6,000

    Re: Countif based on date range

    Happy to help

+ 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] Single column countif with moving range based on today's date
    By JonesyCC in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-16-2013, 02:45 PM
  2. COUNTIF date will expire in a date range
    By neilanderson in forum Excel General
    Replies: 6
    Last Post: 07-31-2012, 02:08 PM
  3. Combining a Date Range COUNTIF and a general COUNTIF
    By jacobtom in forum Excel General
    Replies: 1
    Last Post: 09-15-2011, 05:06 PM
  4. countif based on a date range
    By mamig in forum Excel General
    Replies: 3
    Last Post: 01-13-2010, 12:35 PM
  5. Using COUNTIF based on a date range
    By avidcat in forum Excel General
    Replies: 3
    Last Post: 01-08-2010, 12:27 PM

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