counting dates

Copper Contributor

I 've got a file from https://cneos.jpl.nasa.gov/fireballs/  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

@Mike_Kova 

=SUMPRODUCT((MONTH($A$2:$A$926)=C4)*(YEAR($A$2:$A$926)=B4))

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

number of observations.JPG 

@Mike_Kova 

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

=COUNTIFS(A:A,">="&DATE(B2,C2,1),A:A,"<="&EOMONTH(DATE(B2,C2,1),0))

 

Harun24HR_0-1658976236877.png

 

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.

@Mike_Kova 

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.

@Mike_Kova 

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.