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.
SajExcel so my sense (you could experiment with it) is that the reason those formulas aren't working on 2019 and 2020 is simply that the lists are so short they don't meet the necessary conditions for the COUNTIF criteria to be met to produce a figure other than zero, and that creates the error. I don't have time right now to play with it....would love to do so, but just can't. There are others here who probably can look at it and off the tops of their heads give you the answer.
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.
I've noticed some issues with your COUNTIF formula. It's not particularly good in handling blanks and errors. In C1, for instance, the "1/......." causes #DIV/0! errors for the blank cells in range C3:C361. Normally, the AGGREGATE function will allow you to sum ranges and exclude errors, but that doesn't seem to work in combination with the "1/COUNIF(array,array)"
However, the following formula in C1 will work:
=SUMPRODUCT(IFERROR(1/COUNTIF(C3:C361,C3:C361),""))The same is true for D1, but with an additional problem. The VLOOKUP function in column D causes #NA! for the rows where C-rownumber is blank. And all of these #NA! result in one unique bird name "#NA!". The above formula (copied to D1) results in 55 where this should be 54. Thus, D1 should have the following formula:
=SUMPRODUCT(IFERROR(1/COUNTIF(D3:D361,D3:D361),""))-1This will solve your counting of unique bird in C1 and D1, both in sheets 2019 and 2020. But I'm sorry to admit that I don't understand what you want to see in columns J and K. Perhaps you can explain a bit more.
By the way, depending on the Excel version you are on, you may have to enter the above formulae with Shift-Ctrl-Enter. That will put curly brackets { } around it all.
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.
