Count By Year & Month

  • I am working on an excel sheet where I enter dates for registered events. I need to count the number of days registered for any month. For example, Column B contains date of event, column C contains number of events for that particular date.


    I need to get the following results:
    - How many dates registered in the Month of January (or any month), I assumed this is the number of dates that were entered for the month January.
    - How manu events registered in the Month of January (or any month), I assumed this is the sum of the numbers in column C that match the dates of the month January.


    I need the formula for this example because I have other counts I need to calculate per month, such as number of people registered, etc.


    Thanks

  • Re: Count Number Of Days Occured For Certain Month In Date Column


    For the number of days (01/2008) :
    =SUMPRODUCT((YEAR(A1:B100)=2008)*(MONTH(B1:B100)=1))
    For the number of events :
    =SUMPRODUCT((YEAR(A1:B100)=2008)*(MONTH(B1:B100)=1)*C1:C100)
    Regards.
    Daniel

  • Re: Count Number Of Days Occured For Certain Month In Date Column


    Thanks daniel that worked for the same sheet. I apologize for not mentioning this earlier but the count results in a sheet and data in a different sheet. Another thing is, how can I define the range to be a whole column instead of specifiying it from row 1 to row 100.


    In the formula you provided above the range for column be is A1:B100, why that and not A:A? same thing applies for column C. I need it to refer to a whole column.


    Thanks again

  • Re: Count Number Of Days Occured For Certain Month In Date Column


    Suppose the data is on Sheet1 and the formulae on another sheet :
    =SUMPRODUCT((YEAR(Sheet2!B1:B100)=2008)*(MONTH(Sheet2!B1:B100)=1))
    =SUMPRODUCT((YEAR(Sheet2!B1:B100)=2008)*(MONTH(Sheet2!B1:B100)=1)*Sheet2!C1:C100)
    Daniel

  • Re: Count Number Of Days Occured For Certain Month In Date Column


    Use dynamic named ranges that will expand/contract when you add/remove data. There is a tutorial and there are plenty of posts on using named ranges in this forum. Hover over the word Range to see them.


    Cheers,
    Averil

    -------------
    [FONT="Comic Sans MS"]Averil Pretty[/FONT]

  • Re: Count Number Of Days Occured For Certain Month In Date Column


    Well, for a better understanding of the formula, I think it's best to use the range addresses (showing they must have the same size). Furthermore, defining a named range is not always possible, according to the design of the sheet.
    Regards.
    Daniel

  • Re: Count Number Of Days Occured For Certain Month In Date Column


    Quote from daniel.c

    For the number of days (01/2008) :
    =SUMPRODUCT((YEAR(A1:B100)=2008)*(MONTH(B1:B100)=1))
    For the number of events :
    =SUMPRODUCT((YEAR(A1:B100)=2008)*(MONTH(B1:B100)=1)*C1:C100)
    Regards.
    Daniel


    Thanks Daniel,
    I tried the first formula and worked but the second one gave me #Value error[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Averilp:
    Thanks I searched the internet to understand more about ranges. But I still have lots to learn I think.


    Dave:
    How can PivotTable be useful for my example?


    Ta

  • Re: Count By Year & Month


    Was wrong. Formulae should be :
    =SUMPRODUCT((YEAR(B1:B100)=2008)*(MONTH(B1:B100)=1))
    and :
    =SUMPRODUCT((YEAR(B1:B100)=2008)*(MONTH(B1:B100)=1)*C1:C100)
    If there are still errors, then you certainly have non numeric cells in C1:C100; change to (array formula, validate with CTRL+Shift+Enter) :
    =SUMPRODUCT((YEAR(B1:B100)=2008)*(MONTH(B1:B100)=1)*IF(ISNUMBER(C1:C100),C1:C100,0))
    Regards.
    Daniel

  • Re: Count By Year & Month


    Daniel,
    I have changed the format of the cells to ne numeric cells. The formula works exactly as it is, but when I select the whole column, it give me an error. I assumed because the cells are still empty. But it should only do the counting and the sum of nonblank cells. Do you think this would solve the error?

  • Re: Count By Year & Month


    You can't enter columns in an array formula. You can enter A1:A65535 but not A:A. Another drawback is that those formulae are CPU time consuming so that, if you have a lot of them, they will slow the recalculations.
    Now when you have spare time, have a look at dynamic named ranges, as recommended by Averilp; it avoids entering big ranges and adapt to the size of the range to your actual data.
    (Hope it makes sense).
    Regards.
    Daniel

  • Re: Count By Year & Month


    Alright,
    Thanks Daniel, will work on that.


    Dave,
    Thanks, will go and learn it :)




    Daniel and Averilp:
    I was just looking at the dynamic named ranges and i followed the steps as mentioned. Even after naming the ranges, I still get the #Value! error.


    Dave:
    I looked into the PivotTables and I tried to create one. I choose the Date field and the number of registers field. It displayed the number for each date, the total number for that one particular date as well as the grand total. I want to get the sum of registers for a whole month of January for example. How can I change that in PivotTable to have the month displayed instead of displaying each date.

  • Re: Count By Year & Month


    Just a quick note, I was still trying to get this formula to work:
    SUMPRODUCT((YEAR(DB!B2:B3)=2008)*(MONTH(DB!B2:B3)=1)*(DB!E2:E3))


    I finally knew why it did not work. It only works on nonblank rows. I tried to add the formula to IF formula to check for only rows that are <> 0, and it did not work. I had to define the range to be exactly those rows that are nonblank.


    How can I make the formula work for rows that are not blank?


    I have tried this formula and it returned the number of records entered for that month:
    SUMIF(DB!E2:E65536, SUMPRODUCT((YEAR(DB!B2:B65536)=2008)*(MONTH(DB!B2:B65536)=3)),DB!E2:E65536)


    I wanted it to sum the values in column E that are recorded for a particular month.

  • Re: Count By Year &amp; Month


    The formula works with blank cells, but not with non numeric cells.
    Use (array formula, validate with CTRL+Shift+Enter) :
    =SUM(IF(ISNUMBER(DB!B2:B65536),(YEAR(DB!B2:B65536)=2008)*(MONTH(DB!B2:B65536)=1),0)*(IF(ISNUMBER(DB!E2:E65536),DB!E2:E65536,0)))
    Note that the formula is not bullet proof, if you have negative numbers or numbers greater than 9999 in column B. That may be added.
    I'll post you later on a sample workbook with dynamic range names.
    Regards.
    Daniel[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Consider the attached workbook. First look at sheet Sheet1; that is the simplest case where there are no blank cells within the range.
    I defined two names (Insert, Name, Define)
    1. "Sh1Dates" which defines the range of dates in column B. It is defined by the formula :
    =OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B))
    2. "Sh1Values" which represents the values of column E :
    =OFFSET(Sheet1!$E$2,,,COUNTA(Sheet1!$B:$B))
    Which means from E2 for a length equal to the number of non blank cells in column B.
    Note that if cell B1 or E1 are non blank cell(s), you have to substract 1 from that number.
    The formula becomes (array formula) :
    =SUM(IF(ISNUMBER(Sh1Dates),(YEAR(Sh1Dates)=2008)*(MONTH(Sh1Dates)=1),0)*(IF(ISNUMBER(Sh1Values),Sh1Values,0)))
    Consider now sheet DB :
    There are blank cells within the range. I have to use an intermediate (in H1) to obtain the last used cell row. The formula in H1 is (array formula) :
    =MAX((B2:B65536<>"")*ROW(2:65536),(E2:E65536<>"")*ROW(2:65536))
    The named ranges "rgDates" and "rgValues" are defined by the respective formulae :
    =OFFSET(DB!$B$2,,,DB!$H$1-1)
    and
    =OFFSET(DB!$E$2,,,DB!$H$1-1)
    The final formula in I1 (array formula) is :
    =SUM(IF(ISNUMBER(Sh1Dates),(YEAR(Sh1Dates)=2008)*(MONTH(Sh1Dates)=1),0)*(IF(ISNUMBER(Sh1Values),Sh1Values,0)))
    Hope it makes sense.
    Regards.
    Daniel

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!