counting dates

Occasional Contributor

I 've got a file from  I removed all columns but the first one. The first column shows the dates when a fireball was observed.    I need to arrange it in this form
2022/6/    number of observations in this month

2022/5/   number of observation in this month

2022/4/ number of observations in this month


2022/1/ number of observations in this month

2021/12/ number of observations for this month

and so on.


How do I do it? Thanks to all.

7 Replies



You can try this formula which returns the expected results in my sheet.

number of observations.JPG 


Use COUNTIFS() function with dates criterial. Try following function as per screenshot. Also see attached file.





Thank you. Can you send me the spreadsheet?
Thanks, but it does not seem to work. Do I need to do it line by line? I am trying to automate it so that it can be done for all lines at once.


First, it work as it can been in the uploaded workbook.

Second, you can do it with a pivot table.

Peak Brightness Date/Time (UT) in rows area and group by years and months and sort in descending order.

Peak Brightness Date/Time (UT) in values area. If necessary change the aggregation to Count.


You are welcome. Here you are.

@Mike_Kova     I just want to thank everyone who answered. You are great guys. I wish the Customer Support was 10% as helpful and knowledgeable as you are.