Forum Discussion
Formulas to calculate total and other bits
Introduced a few helper columns and used Sergei's formula (made range dynamic) to count unique observations up to the date of observation. This gives you the "All-time sequence" in one column. The same formula (with adjusted ranges) entered from each beginning of the new year gives you the Yearly sequence.
Using the filter buttons I can see that you saw the Carrion Crow 8 times in total. It was the 8th one in 2018, the 11th in 2019 and the 1st in 2020.
Perhaps not the prettiest solution, but it works. And, I believe it's fairly easy to understand and maintain.
Thank you Riny_van_Eekelen , Sergei Baklan and @mathetes
I have made some changes to the spreadsheet following your advice. See attached.
1) I have transferred all of the data on to one sheet, so all three years are located on the first sheet.
2) I have used your suggested formula and this seems to work for C1.
3) I have decided to dispense with the formula in D1 because, this is essentially the same thing - this column just shows the scientific name of the bird and because my list is only British and some of the birds are from other regions, this one won't work, until I update the source list. This is something I can think about later.
Here is what I still need some help with, if I may:
1) Sequence - I would like this column to sequence the birds in order of date, with a number. So for example, the earliest bird I saw will be 1, with the most recent bird I saw being the highest number. Of course, this should only be based on unique species. By this, I mean, if I have seen the same bird twice, it only gets a number the first time I have seen it.
2) Year list - I would like this to work in the same way as sequence but to restart every year. So, for example the first bird I saw in 2020 was a Carrion Crow, so this should ideally state 1/2020 and the same for the other birds in other years. Again, if I have seen the same bird twice in a year I don't want it to get a number again. It should show the sequence of species for that year.
Where I don't have a full date, I can assume this is the last date of the year and where there are several new species for the same date, I am happy for them to be ordered as per the rows of the spreadsheet.
I hope this makes sense. Thanks so much for your help so far.
Best wishes.
Introduced a few helper columns and used Sergei's formula (made range dynamic) to count unique observations up to the date of observation. This gives you the "All-time sequence" in one column. The same formula (with adjusted ranges) entered from each beginning of the new year gives you the Yearly sequence.
Using the filter buttons I can see that you saw the Carrion Crow 8 times in total. It was the 8th one in 2018, the 11th in 2019 and the 1st in 2020.
Perhaps not the prettiest solution, but it works. And, I believe it's fairly easy to understand and maintain.
Riny_van_Eekelen thanks very much. This is excellent and does everything I need it to now.
Best wishes.