Need help with counting occurance for multiple people at once

**Junny** · 04-01-2015, 08:27 PM

Hi excel forum,
Im new here, and would need some advice and help with my database problem.

Data:
Name Year
Tom 2013
Tom 2014

Tom 2015
Tom 2015
Mike 2012
Mike 2012

(Space is intentional as there are cases which have such occurance, where there are blank entries)
I need a formula which could help me count the number of years that occurances did happen for each person,
And each year should only be count once.
I had one previously:
=sumproduct((P2:P500<>"")/countif(p2:p500,p2:p500&""))

But this only works for single person at any one time.
I would require one that allow me to chek multiple entries at once.

Do help and thankyou for your help

Have a nice day!

**JBeaucaire** · 04-01-2015, 09:15 PM

This is an array formula, so you must limit how many cells you put this formula into as arrays are intense resource hogs.

Assuming:
1) Names are in P2:P500
2) Years are in Q2:Q500
3) You have already extracted the unique list of names using Data > Filter from column P and put them into a column ready for calculation, let's say this unique list is starting at Y2
4) This array formula in Z2 will provide the first answer:

=SUM(IF(FREQUENCY(IF($P$2:$P$500=Y2, $Q$2:$Q$500), IF($P$2:$P$500=Y2, $Q$2:$Q$500)) > 0, 1))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Once the first correct answer appears, copy Z2 down as far as your unique list of names in column Y.