3
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.
So are 5 and 3 the two scores A had in 2011? Is that how the data is setup? And why wouldn't a pivot table work? – Raystafarian – 2014-01-17T15:56:52.870
@Raystafarian Whoa, this just blew my mind. You just solved my problem and TIL what a pivot table is. Thank you!! – Dre – 2014-01-17T16:08:42.137
Awesome! They are pretty powerful and have become a lot more easy to use since Excel 2007 – Raystafarian – 2014-01-17T16:13:51.870
Powerful, indeed. And to answer your question, yes, person A had two scores (i.e. 5, 3) in 2011, but only had one score (i.e. 2) in 2012, etc. Eventually, when I am allowed to (all these rules...), I will post a detailed answer for this question. Thanks again. – Dre – 2014-01-17T16:51:34.613