# counting dates

Occasional Contributor

# counting dates

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

# Re: counting dates

``=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.

# Re: counting dates

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))``

# Re: counting dates

Thank you. Can you send me the spreadsheet?

# Re: counting dates

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.

# Re: counting dates

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.

# Re: counting dates

You are welcome. Here you are.

# Re: counting dates

@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.