I am trying to write an excel formula (or formulas) to count the first occurrence of a value in a given year for each Person in my table. I've searched for related questions on Superuser to no avail. To give you some context, each Person participated in one or more years at an exam. During each exam year, they were given one or more scores (values 1 through 9 in the table). For example, Person A had two scores (5,3) in 2011, one score (2) in 2012, and two scores (4,1) in 2013. However, I am only interested in tallying how many years each Person had participated and I don't care which years they were. My data follows:
Person 2011 2012 2013 A 5 A 3 A 2 A 4 A 1 B 7 B 6 C 1 C 9 C 4 C 2 D 5
This is the result I want:
Person Years A 3 B 2 C 2 D 1
I've tried so many convoluted/inefficient ways to achieve this result using a combination of SUM, COUNTIFS, and VLOOKUP, but it always counted all values and not first occurrence of a value in a given year. I just can't figure out how to do it or if it's even possible using excel functions. I don't know VBA, but I suspect it may be required for this. Thanks in advance for your help.