Finding unique combinations across 3 columns using a function

Dear all,

Here's the situation. I have 3 columns. Column 1 has years (2010-2011, 2011-2012, 2012-2013, etc.). Column 2 has periods (P1 P2, P3, etc.), Column 3 has names of people. (see below)

How do I find the number of unique occurences for a name associated with same periods and same years. For example, there are 2 occurences for Goldberg, Jane, in P14, in 2012-2013, 1 occurence for her name in 2009-2010, P1 and one other unique occurence for her name in 2009-2010, P12.

I want to be able to find the number of those occurences using a formula (not by using filters).

Thank you for your help!


Year Periode Name
2012-2013 P1 Smith, John
2011-2012 P14 Blaum, John
2012-2013 P14 Goldberg, Jane
2012-2013 P14 Goldberg, Jane
2009-2010 P1 Goldberg, Jane
2012-2013 P2 Ahluwalia, Harveen
2010-2011 P3 Alaoui, Ismail
2010-2011 P4 Alaoui, Woualid
2012-2013 P5 Alborzi, Zoubin
2011-2012 P6 Allen, David
2012-2013 P7 Smith, Henry
2009-2010 P8 Amato, Carmelo
2011-2012 P9 Roosevelt, Anderson
2012-2013 P10 Roosevelt, Anderson
2009-2010 P11 Mbogo, Togo
2009-2010 P12 Goldberg, Jane
2012-2013 P13 Abramovitz, John
2012-2013 P15 Lisitsa, Carolina
2010-2011 P15 Lisitsa, Carolina
2010-2011 P16 Ascah, John Edward
2012-2013 P17 Bianci, Antonio
2009-2010 P18 Atkins, Charles
2012-2013 P19 Johnson, Eric
2010-2011 P20 Johnson, Eric
2012-2013 P21 Baldassarre, Carolina
2011-2012 P22 Baras, Ahmed-Youssef

test2.xlsx

Originally Posted by :) Sixthsense :)

Please attach a sample workbook with expected output for better understanding

File attached.

Re-reading my question, I think I phrased it incorrectly. What I need is to know, for each combination of year and period, how many unique names there are. You can see the expected output in the file.

Originally Posted by :) Sixthsense :)

Can be done easily with the help of pivot table, refer the attached file for details

Thanks, but I should have mentioned that for the purpose of what I'm doing I need this to be done in the form of a function.

Is there a way to do that?

@ Sixthsense: thanks for your help, but I really need this to be in functions form. As i add data I need the output to update automatically, I cannot re-create pivot tables.

@Fotis: thanks for your help. I tried your formula, the problem is that it counts every occurence of year and period combinations. eg.: for "2012-2013 and P14" the output of your formula is 3 whereas is should be 2 (Smith, John and Goldberg, Jane). your formula counts Goldberg Jane twice.

Any thoughts?

Fotis, I think this may just work actually!! thank you so much!