Jul 27 2022 04:22 PM - edited Jul 27 2022 04:25 PM
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.
Jul 27 2022 04:54 PM
=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.
Jul 27 2022 07:44 PM
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))
Jul 28 2022 05:14 PM
Jul 28 2022 06:44 PM - edited Jul 28 2022 06:44 PM
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.
Jul 29 2022 02:04 AM
You are welcome. Here you are.
Aug 03 2022 02:50 PM
@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.