+ Reply to Thread
Results 1 to 3 of 3

Count number of dates in a column occuring in a specific month and specific year

  1. #1
    Registered User
    Join Date
    04-30-2010
    Location
    washington dc
    MS-Off Ver
    Excel 2007
    Posts
    51

    Count number of dates in a column occuring in a specific month and specific year

    Hello -

    I have a spreadsheet with a date column that has about 10,000 dates (format dd/mm/yyyy) over the course of 3 years. I am looking for a formula that counts the number of entries by month/year (ie, the number of entries that fall in March, 2013.)

    I have attached an example spreadsheet. The entries I need to count is in column N. You can see the table I developed and the formula I was trying to use:

    =SUMPRODUCT(--(ISNUMBER(S$2:$S$50)),(MONTH($S$2:$S$50)=(MONTH(M53))))
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Count number of dates in a column occuring in a specific month and specific year

    I think that Pivot Table would be the best solution for you.
    use your column N as both row headers and data values. group row headers by months AND years.


    Kopia Example BTS.xlsx

    By the way - you have data in column N, while formula in your spreadsheet addresses data in column S - is there rationale behind it?

    Best Regards

  3. #3
    Registered User
    Join Date
    04-30-2010
    Location
    washington dc
    MS-Off Ver
    Excel 2007
    Posts
    51

    Re: Count number of dates in a column occuring in a specific month and specific year

    Hi Kaper -

    I did think about using a pivot table, but would also like to find a formula that does the same. Any thoughts?

+ 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] Formula to count entries as per month, year and submission wise
    By hanidean in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 05-26-2013, 11:52 PM
  2. Replies: 1
    Last Post: 03-05-2011, 04:18 PM
  3. Counting Entries by Month and Year
    By ImaGina in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-05-2006, 02:11 AM

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