Count number of dates by month

  • Hi Folks


    I have a formulae that counts the number of dates in a column by the month that they are in:


    =SUMPRODUCT(--(MONTH(A2:A398)=B2))


    However, although it works for months 2-12, if I select 1 to count any dates in January it counts the entire range?? For example the column has 397 cells, if I leave them all empty it counts 397.


    If I enter a date, say, 01/02/2021 and select 2 in B2 it counts 1, if I then reselect 1 in B2 it counts 396??? What am I doing wrong, it's driving me nuts...


    Really hoping someone can help please.


    Kind regards and thanks in advance :-)


    DezB

  • Ho Roy.


    Many thanks for getting back to me, much appreciated. I'm still having problems and can't figure out why. I've attached a sample and would really appreciate it if you could tell me where I'm going wrong please?


    I've tried various formulae but all do the same thing except yours which only counts one occurrance of a month selected, the others just count all entries as the same month, really peculiar.


    Kind regards


    DezB


    by_month.xlsx

  • I think you had entered my formula as an array formula, which is wrong.


    I've converted the column of dates to an Excel Table so that you can add or remove dates and the formulas will still work.