Calculate number of entries for a particular month

[eddymontreal]

Good day,

I have a column with Date entries in this format "16-JAN".

Example:

16-JAN
27-FEB
27-FEB
3-MAR
3-MAR
4-MAR

I'd like to know what would be the function to calculate how man
entries I have for a particular month. In my example above, th
results would be: 1 for Jan. 2 for Feb. 3 for March, etc.

Please note that the date can be the same (like for March in th
example). What I need is the total for each month.

Thank you for your help!

Edd

 

[Paul]

Good day,

I have a column with Date entries in this format "16-JAN".

Example:

16-JAN
27-FEB
27-FEB
3-MAR
3-MAR
4-MAR

I'd like to know what would be the function to calculate how many
entries I have for a particular month. In my example above, the
results would be: 1 for Jan. 2 for Feb. 3 for March, etc.

Please note that the date can be the same (like for March in the
example). What I need is the total for each month.

Thank you for your help!

Eddy

If your dates are real dates (not just text), a formula such as this will
work for January:
=SUMPRODUCT(--(MONTH(A1:A6)=1))
Just change =1 to =2 for Feb, etc.

For any month other than Jan, it doesn't matter if your range contains
blanks. If you might have blanks (for example, you want to be able to use a
range longer than your current data to allow for additions), modify the
january formula thus:
=SUMPRODUCT(--(MONTH(A1:A100)=1),--(A1:A100>0))

 

[Andy B]

Hi

Try this:

=SUMPRODUCT((MONTH(B10:B15)=1)*1)
for Jan
=SUMPRODUCT((MONTH(B10:B15)=2)*1)
for Feb
=SUMPRODUCT((MONTH(B10:B15)=3)*1)
for Mar

Andy

 

[Bernard V Liengme]

Hi Eddy,
We need to know if the cells contain Text or Dates?
If Text (as is suggested by capital letters) then one of these works
=COUNTIF(A1:A6,"*Jan*") or =SUMPRODUCT((RIGHT(A1:A6,3)="Jan")*1
If Dates use =SUMPRODUCT((MONTH(A1:A6)=1)*1)
In all cases make the range reference absolute ($A1:$A6) so you can copy and
modify formula for different months.
Best wishes
Bernard