Calculate number of entries for a particular month

  • Thread starter Thread starter eddymontreal
  • Start date Start date
E

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
 
eddymontreal > said:
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
Click to expand...

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))
 
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
 
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
 
Back
Top