+ Reply to Thread
Results 1 to 5 of 5

Count number of event types occuring by month (SUMPRODUCT?)

  1. #1
    Registered User
    Join Date
    09-23-2015
    Location
    Kamloops, BC
    MS-Off Ver
    2010
    Posts
    6

    Count number of event types occuring by month (SUMPRODUCT?)

    Hi all,

    I'm trying to count the number of events that happens in a certain month, where my date is in put in the format mm/dd/yyyy.

    For example , from C3:C200 are dates and in D3:D200 are events A, B, C, D. I want to know how many times A happens in Jan, B in Jan, A in Feb etc. etc.

    I've tried using SUMPRODUCT with multiple conditions but I keep receiving error messages.

    Any advice?

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Count number of event types occuring by month (SUMPRODUCT?)

    Assuming C3:C200 are real dates.

    Try
    =SUMPRODUCT(--(C3:C200>=DATE(2015,1,1)),--(C3:C200<=DATE(2015,1,31)),--(D3:D200="A"))

  3. #3
    Valued Forum Contributor
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2010
    Posts
    476

    Re: Count number of event types occuring by month (SUMPRODUCT?)

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    1,2....-month number.

  4. #4
    Registered User
    Join Date
    05-31-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    53

    Re: Count number of event types occuring by month (SUMPRODUCT?)

    Hi John, I have not seen the use of --, what does this mean? Thanks!


    I have mocked up a template, assuming F2 will always be the first day of the month. It counts anything bigger or equal to the first day of the month and anything small than the end of that month for the criteria in Row1.
    Excel - CountIfs.xlsx
    If you like my answer please *Add Reputation

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Count number of event types occuring by month (SUMPRODUCT?)

    In the context of the sumproduct, -- is converting an array of TRUE/FALSE responses to 1/0
    {True,True,False,True,False,False) becomes {1,1,0,1,0,0}

+ 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