Results 1 to 2 of 2

Thread: Counting the number of Events that occur in a specific month in a range of days.

  1. #1

    Question Counting the number of Events that occur in a specific month in a range of days.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Good Day;
    I am trying to figure out how to count the number of Events that have occurred for each month in a year and then place that number into another table which then gets graphed. This will cover five (5) years so I would like an easier way than what I have kluged together.

    The only way I have been able to get it to work is to use a COUNTIF formula but I have to first convert the year and month I am interested from a date value (yyyy-mm) to a numerical date value and then manually enter those numerical date values into the COUNTIF formula.

    This is my COUNTIF formula that I am using: =COUNTIFS($C$2:$C$23,">=41640",$C$2:$C$23,"<41671")

    $C$2:$C$23 is the range of dates (in Date format yyyy-mm-dd).
    41640 is 01 Jan 2014 in numerical date value.
    41671 is 01 Feb 2014 in numerical date value.

    Any ideas how I might do this?

    I have attached my spreadsheet so you have a good idea of what I am trying to do.

    Thanks for the help.


    Kevin
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,512
    Articles
    0
    Excel Version
    Excel 2016
    You could use EOMONTH() to get you the first day and last day of each month...

    In H2:

    =COUNTIFS($C$2:$C$23,">="&EOMONTH(E2,-1)+1,$C$2:$C$23,"<="&EOMONTH(E2,0))


    copied down.


Posting Permissions

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