+ Reply to Thread
Results 1 to 10 of 10

COUNTIFS With Date Range

  1. 09-21-2009, 09:52 AM #1
    OlYeller21
    OlYeller21 is offline
    Registered User
    Join Date
    07-12-2006
    Location
    Ohio
    MS-Off Ver
    Newest
    Posts
    49

    COUNTIFS With Date Range

    Hey,

    I need help with a COUNTIFS function (I think that will be the best function to use at least).

    I have a two columns of data, column one is dates (mm/dd/yyyy) and column two is states (AK, MA, MI, etc). I need a way to count how many times a states comes up for a certain month. For example, I need to know how many times MA appears through the dates 1/1/2007 and 1/31/2007.

    Here's what I have so far. It works but I can only specify one date. Any help you can provide would be greatly appreciated.

    =COUNTIFS('SHEET NAME'!D:D, "1/5/2007", 'SHEET NAME'!E:E, "MA")
    Last edited by NBVC; 09-21-2009 at 01:01 PM.
    Reply With QuoteRegister To Reply
  2. 09-21-2009, 09:57 AM #2
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: COUNTIFS With Date Range

    Yes you can adapt:

    Please Login or Register  to view this content.
    Although COUNTIFS is quite efficient best to avoid using entire column references if possible.
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  3. 09-21-2009, 10:37 AM #3
    OlYeller21
    OlYeller21 is offline
    Registered User
    Join Date
    07-12-2006
    Location
    Ohio
    MS-Off Ver
    Newest
    Posts
    49

    Re: COUNTIFS With Date Range

    Thanks for the help! That worked perfectly.

    So that I don't have to go in manually and adjust the dates and states, is there a way to point those criteria at the value in a cell? For instance, C38 is "1/1/2007", C39 is "1/31/2007", and A19 is "AK".

    The line of code is:

    Please Login or Register  to view this content.
    I'm getting a formula error. I'm betting it has to do with quotes around the date cells but I'm not sure.

    Again, any help would be greatly appreciated.
    Reply With QuoteRegister To Reply
  4. 09-21-2009, 10:46 AM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: COUNTIFS With Date Range

    Should be:

    =COUNTIFS('SHEET NAME'!D:D,">="&C38,'SHEET NAME'!D:D,C39, 'SHEET NAME'!E:E, A19)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  5. 09-21-2009, 10:50 AM #5
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: COUNTIFS With Date Range

    Also missing the <= operator re: C39 I think.
    Reply With QuoteRegister To Reply
  6. 09-21-2009, 11:25 AM #6
    OlYeller21
    OlYeller21 is offline
    Registered User
    Join Date
    07-12-2006
    Location
    Ohio
    MS-Off Ver
    Newest
    Posts
    49

    Re: COUNTIFS With Date Range

    Ya, I caught the missing operator. Thanks for the help guys. That worked perfectly.
    Reply With QuoteRegister To Reply
  7. 08-02-2012, 08:27 AM #7
    admirable
    admirable is offline
    Registered User
    Join Date
    08-02-2012
    Location
    Pakistan
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    43

    Re: COUNTIFS With Date Range

    Quote Originally Posted by NBVC View Post
    Should be:

    =COUNTIFS('SHEET NAME'!D:D,">="&C38,'SHEET NAME'!D:D,C39, 'SHEET NAME'!E:E, A19)
    What is "A19"? For which it to be used.?

    ---------- Post added at 05:27 PM ---------- Previous post was at 05:14 PM ----------

    Yup... I got it...
    Please Login or Register  to view this content.
    Thanks for everyone for their support.
    Reply With QuoteRegister To Reply
  8. 08-02-2012, 09:00 AM #8
    Cutter
    Cutter is offline
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: COUNTIFS With Date Range

    Hello admirable, and welcome to the forum.

    Please keep the following rule in mind when using the forum:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    Reply With QuoteRegister To Reply
  9. 08-02-2012, 04:29 PM #9
    admirable
    admirable is offline
    Registered User
    Join Date
    08-02-2012
    Location
    Pakistan
    MS-Off Ver
    Excel 2010, Excel 2013
    Posts
    43

    Re: COUNTIFS With Date Range

    Ok.. Moderator.. I Do comply...
    Reply With QuoteRegister To Reply
  10. 12-03-2013, 08:32 AM #10
    OhioTBear24
    OhioTBear24 is offline
    Registered User
    Join Date
    12-03-2013
    Location
    Xenia, Oh
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: COUNTIFS With Date Range

    This also helped me as well. Thank you!
    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)

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