Unique count based on year excluding duplicate values from previous years

[Estevaoba]

Good evening all.

I got stuck in this query from a Brazilian forum.
I'd appreciate your help to count unique values in column C, based on year in column D.
The difficulty is to exclude those values that have already been counted in previous years.

I managed to pull this out using a helper column to mark out duplicates from previous years, but to do it right, I had to sort data down by year.
The author says he can't use that solution.

In file attached, I list 4 different formulas, which I tried to tackle to work as desired to no avail.

Either formula or VBA solution is welcome.
Thanks in advance to anyone who will give it a try.

[MarvinP]

Hi Estevaoba,

Here is what gives your answer.
1. sort your table by year - small to large
2. create a helper column that counts the occurrence of the Scientific Name
3. create a pivot table and filter by the count helper
4. Show the running total by year.
See the attached for the answer.
Countifs no duplicates Pivot Run Total.xlsx

[FlameRetired]

If you would still like a unique counts formula this returns your expected counts (as shown in G9:M9).

You may have to array enter this formula in Excel 2016.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. In cell G5 and filled across

Please Login or Register  to view this content.

[Estevaoba]

Wow! That is awesome, Dave! Thanks a lot!
I knew there had to be some way to get around it. You're the best.

Thank you also, MarvinP.

God bless you all.

[FlameRetired]

You are welcome. Glad it helped. Thank you for the feedback, added rep and marking your thread Solved.