Results 1 to 9 of 9

Count Number of times a month appears in a range of dates

This is a discussion on Count Number of times a month appears in a range of dates within the Excel Questions forums, part of the Question Forums category; Hi All, I have a very large spreadsheet consisting of cycle times and dates. I need to count how many ...

  1. #1
    New Member
    Join Date
    Jul 2008
    Posts
    6

    Default Count Number of times a month appears in a range of dates

    Hi All,

    I have a very large spreadsheet consisting of cycle times and dates. I need to count how many time times a specific cycle was performed within certain months.

    I have tried everything I can think of but nothing seems to be working.

    I've tried using COUNTIF etc but nothing!!!

    Can any of you please help!???!!!

    Regards,
    K.

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Location
    Hosur, India
    Posts
    175

    Default Re: Count Number of times a month appears in a range of dates

    Please give a set of example for better understanding.

  3. #3
    Board Regular
    Join Date
    Nov 2005
    Location
    Dallas, TX
    Posts
    272

    Default Re: Count Number of times a month appears in a range of dates

    Try this,

    =SUMPRODUCT((RANGE_of_DATES>=BDate)*(RANGE_of_DATES<=EDate))

    Regards

  4. #4
    New Member
    Join Date
    Jul 2008
    Posts
    6

    Default Re: Count Number of times a month appears in a range of dates

    Hi, Sorry about that.

    Say for example I need to count how many times BD takes place in the month of April? Multilple cycles take place on the same day.

    Column A Column B
    28-Apr-08 BD
    29-Apr-08 BD
    29-Apr-08 FS
    29-Apr-08 WU
    30-Apr-08 LT
    30-Apr-08 BD
    1-May-08 WU
    1-May-08 WU
    2-May-08 BD

    Thanks a million,
    Katie

  5. #5
    Board Regular
    Join Date
    Nov 2008
    Location
    Hosur, India
    Posts
    175

    Default Re: Count Number of times a month appears in a range of dates

    Enter the month (in numbers 4 for April) in Cell C1 and enter the cycle in Cell D1. Use the below formula
    =SUMPRODUCT(--(MONTH(A1:A9)=C1)*--(B1:B9=D1))

    Hope this is as per your expectation.

  6. #6
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    19,958

    Default Re: Count Number of times a month appears in a range of dates

    Quote Originally Posted by ryan.katy View Post
    Say for example I need to count how many times BD takes place in the month of April? Multilple cycles take place on the same day.
    So, if BD takes place twice on the same day [in April] do you count it only once.....or twice?

  7. #7
    New Member
    Join Date
    Jul 2008
    Posts
    6

    Default Re: Count Number of times a month appears in a range of dates

    Hey,

    Yeah I need to count it twice.

    At the moment I have a countif defining the range of dates and searching for BD say within that range. But cycle programs are ever changing!!!

  8. #8
    Board Regular
    Join Date
    Nov 2008
    Location
    Hosur, India
    Posts
    175

    Default Re: Count Number of times a month appears in a range of dates

    If you have fixed month and cycle times then you can use the formula mentioned already.
    But if it is dynamic, best way of handle this is to use Pivot table. Once you have created a pivot table (except the range changes) you can just refresh to get the details required.

    Hope this is useful for you.

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,898

    Default Re: Count Number of times a month appears in a range of dates

    Quote Originally Posted by ryan.katy View Post
    Hey,

    Yeah I need to count it twice.

    At the moment I have a countif defining the range of dates and searching for BD say within that range. But cycle programs are ever changing!!!
    Code:
    =SUMPRODUCT(
      --($A$2:$A$20-DAY($A$2:$A$20)+1=E2),
      --($B$2:$B$20=F2))
    where E2 houses a month/year of interest in the form of:

    1-Apr-08

    meaning Apr/2008

    and F2 a value like BD.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com