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.
mathetes I've tried that and it doesn't work. Thanks for your advice anyway.
If anyone else out there is able to help, please let me know. 🙂
As variant, to exclude blanks from calculations and avoid array formula that could be
=SUMPRODUCT((C3:C361<>"")/(COUNTIF(C3:C361,C3:C361&"")))The source is here Count unique values in a range with COUNTIF
In the file the formula is in 2019!C1.
Exceljet: what an excellent resource!! Thanks for that link.
