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? Jan 17 '14 at 15:56
  • @Raystafarian Whoa, this just blew my mind. You just solved my problem and TIL what a pivot table is. Thank you!!
    – Dre
    Jan 17 '14 at 16:08
  • Awesome! They are pretty powerful and have become a lot more easy to use since Excel 2007 Jan 17 '14 at 16:13
  • 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
    Jan 17 '14 at 16:51

Sounds like a job for pivot table!

Highlight your table and go to Insert → pivot table and arrange it how you want. I'd do the following -

enter image description here

  • The SUM value field setting is useful, but I'd like to point out to other observers that, in my case, I don't want the sum of the scores in each cell, but a count of the first occurrence of a score (don't care what value) in each of the years 2011, 2012, and 2013.
    – Dre
    Jan 20 '14 at 14:54
  • @dre this screenshot is not perfect for your particular problem. Please ignore. Jan 20 '14 at 19:34

Thanks to Raystafarian's comment, I am able to provide a detailed answer to my own question. Using a pivot table, I select my entire table range. In the PivotTable Field List dialogue, I add Person to the report, then drag each year (i.e. 2011, 2012, 2013) unchecked into the Values field. Doing so gives me this initial result:

Row Labels     Count of 2011    Count of 2012   Count of 2013
A              2                1               2
B              1                                1
C              2                2   
D              1        
Grand Total    6                3               3

This is fantastic as it not only enables me to COUNT the number of values per row, indicating how many years each person participated, but it also tells me how many scores were given per year for each person.

So in my final step, I simply add the formula =COUNT(B2:D2) in cell E2 whereby Column A = Row Labels, Column B = Count of 2011, Column C = Count of 2012, Column D = Count of 2013, Column E = Years and get this result:

Row Labels     Count of 2011    Count of 2012   Count of 2013     Years
A              2                1               2                 3
B              1                                1                 2
C              2                2                                 2   
D              1                                                  1  
Grand Total    6                3               3                 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.