Forum Discussion
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
- 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.
=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.
- Thank you. Can you send me the spreadsheet?
You are welcome. Here you are.
