Forum Discussion
Formulas to calculate total and other bits
Hi, Is anyone able to help me with some (what I think are) quite simple formulas to complete a bird list? I'm not great on Excel and having a problem carrying things over from another spreadsheet.
Need help with C1 and D1 on 2020 and 2019 sheets and J and K columns please
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.
14 Replies
I'm not an expert on the COUNTIF function, but from reading the help text on that function I see no examples that conform with your use of it, so let me ask you what it is counting in the first place in those columns. What sort of number(s) do you expect to get via those formulas at the top of those columns. IF you're counting (or hoping to count) the number of duplicate entries, then the problem may be simply that you're getting a zero...
On a separate note, have you considered combining 2019 and 2020 into one database, just adding a column to differentiate the years? Generally, the more you can structure your data as a single table, the more powerfully it can serve as the basis for meaningful summary reports or analysis.
mathetes thanks, yes ultimately I would like to combine everything on to one sheet.
The C1 and D1 cells should show the number of unique entries. It works on the 'my list' sheet, but I don't seem to be able to transfer to the others
Also - I want to add formulas for 'sequence' and 'year list.'
Any help appreciated.
You caught me in the middle of editing my first reply.
So it's meant to capture the number of unique sightings. But on the smaller sheets isn't EVERY entry a unique one? I tried editing the formula so that only the COUNTIF part was working, and it produced a zero...which meant a "div by 0" error resulted, and resulted "correctly."
Sometimes the way to diagnose something that isn't working is to break down the formula into several steps.....in this case, doing that for me at least said you need to thing about approaching it in a different fashion. Especially when it IS working on those long lists--and is it working reasonably? You have confidence those answers are correct?--since it seems to be working on those long lists, I have to think that one reason it's not working here is simply that the lists are too short, so everything listed is unique.
Happy to upload the spreadsheet I'm working on
