+ Reply to Thread
Results 1 to 9 of 9

Using Date Range in Countifs formula

  1. #1
    Registered User
    Join Date
    05-09-2010
    Location
    Douglasville, GA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Using Date Range in Countifs formula

    Good morning, everyone. First time to this site and first time poster (I normally use MS Office's Discussion Groups but they are down for some reason.) I have a problem that I am trying to figure out and for the life of me I can't.

    Situation is as follows:
    2 sheets, one is dB_File and the other is Sheet_2
    On Sheet_2, I have a reporting period date listed in cell G1, formatted mm/dd/yyyy, and the employee number in cell G13.
    On dB_File, I have a list of sales, with all the details of the particular sales. Column B has the employee number associated with a particular sale. Column BQ has the date that the sale was made.
    Back to Sheet_2, in cell G17, I am trying to count the number of sales an employee made in a particular MONTH. But I don't know how to structure my countifs criteria so that it counts all sales made in a month, not just a day. Here is what I have so far.

    HTML Code: 
    I want this particular cell to count all of the sales made 3 months before the current reporting period. So, if I am working on April's reporting period, I want to know all of the sales made during the month of January. Any help would be greatly appreciated.
    Last edited by Kenny_D; 05-09-2010 at 10:55 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Using Date Range in Countifs formula

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Using Date Range in Countifs formula

    Assuming G1 has 1st of the month in question this version will give you a count for the whole month

    =COUNTIFS(dB_File!$B$2:$B$99999,$G$13,dB_File!$BQ$2:$BQ$99999,">="&G1,dB_File!$BQ$2:$BQ$99999, "<"&EDATE(G1,1))

  4. #4
    Registered User
    Join Date
    05-09-2010
    Location
    Douglasville, GA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Using Date Range in Countifs formula

    Ok - finally just gave up on IE7 and downloaded IE8 to fix some bugs I was experiencing.

    Now, back to trying to attach a dummy file. See if this helps figure it out.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-09-2010
    Location
    Douglasville, GA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Using Date Range in Countifs formula

    Quote Originally Posted by daddylonglegs View Post
    Assuming G1 has 1st of the month in question this version will give you a count for the whole month

    =COUNTIFS(dB_File!$B$2:$B$99999,$G$13,dB_File!$BQ$2:$BQ$99999,">="&G1,dB_File!$BQ$2:$BQ$99999, "<"&EDATE(G1,1))
    Unfortunately, this formula didn't work. But it has given me an idea I am going to go try.

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Using Date Range in Countifs formula

    That formula worked for me - it gave me a result of 4 for the current month - isn't that the answer?

    For a more generic version you can use this for the same result

    =COUNTIFS(dB_File!$B$2:$B$99999,$G$13,dB_File!$BQ$2:$BQ$99999,">="&EDATE(G1,0),dB_File!$BQ$2:$BQ$99999, "<="&EOMONTH(G1,0))

    change the red zeroes to -1 for previous month, to -2 for the month before that etc.

  7. #7
    Registered User
    Join Date
    05-09-2010
    Location
    Douglasville, GA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Using Date Range in Countifs formula

    daddylonglegs,

    Thanks for the direction. Your second formula was quite useful. I changed it slightly, since I only need to go between the month beginning and month end, to the following:

    Please Login or Register  to view this content.

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

    Re: Using Date Range in Countifs formula

    ....but that would count more than just a single month, it would also include the last date of the previous month so I suggest that the >= should just be >

  9. #9
    Registered User
    Join Date
    05-09-2010
    Location
    Douglasville, GA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Using Date Range in Countifs formula

    Correct you are!! Thanks for identifying that. If you hadn't, it could have been a disaster. I changed the ">=" to just ">".

    Spot on, chap!! :D

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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