COUNTIF with Multiple References - Please Help!!!

[contraububu]

Can someone help me with the following scenario? I would like to find an excel formula that would give me for any changes in the "Name" column, count how many occurrence in the "Effective Date" for that name and display it as Day 1, Day 2, Day 3 etc. or just simply give me the count. Much appreciate your help!

Name	Effective Date	Excel Formula
Michael	09/15/2016	Day 1
Michael	04/01/2016	Day 2
Michael	09/08/2014	Day 3
Frank	10/16/2015	Day 1
Edmond	10/16/2015	Day 1
Lina	04/01/2016	Day 1
Lina	09/01/2015	Day 2
Laura	04/01/2016	Day 1
Laura	02/23/2015	Day 2
Robert	04/01/2016	Day 1
Robert	12/01/2015	Day 2
Steven	12/14/2015	Day 1
Brittany	04/01/2016	Day 1
Brittany	11/01/2016	Day 2
Mitchell	10/31/2016	Day 1
Mitchell	04/01/2016	Day 2
Mitchell	11/01/2015	Day 3

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[LateralThinker]

Hi?

Try this:

	A	B	C
1	Name	Effective Date	Excel Formula
2	Michael	09/15/2016	="Day "&ROW()-MATCH(A2,$A$2:$A$18,FALSE)
3	Michael	04/01/2016	="Day "&ROW()-MATCH(A3,$A$2:$A$18,FALSE)
4	Michael	09/08/2014	="Day "&ROW()-MATCH(A4,$A$2:$A$18,FALSE)
5	Frank	10/16/2015	="Day "&ROW()-MATCH(A5,$A$2:$A$18,FALSE)
6	Edmond	10/16/2015	="Day "&ROW()-MATCH(A6,$A$2:$A$18,FALSE)
7	Lina	04/01/2016	="Day "&ROW()-MATCH(A7,$A$2:$A$18,FALSE)
8	Lina	09/01/2015	="Day "&ROW()-MATCH(A8,$A$2:$A$18,FALSE)
9	Laura	04/01/2016	="Day "&ROW()-MATCH(A9,$A$2:$A$18,FALSE)
10	Laura	02/23/2015	="Day "&ROW()-MATCH(A10,$A$2:$A$18,FALSE)
11	Robert	04/01/2016	="Day "&ROW()-MATCH(A11,$A$2:$A$18,FALSE)
12	Robert	12/01/2015	="Day "&ROW()-MATCH(A12,$A$2:$A$18,FALSE)
13	Steven	12/14/2015	="Day "&ROW()-MATCH(A13,$A$2:$A$18,FALSE)
14	Brittany	04/01/2016	="Day "&ROW()-MATCH(A14,$A$2:$A$18,FALSE)
15	Brittany	11/01/2016	="Day "&ROW()-MATCH(A15,$A$2:$A$18,FALSE)
16	Mitchell	10/31/2016	="Day "&ROW()-MATCH(A16,$A$2:$A$18,FALSE)
17	Mitchell	04/01/2016	="Day "&ROW()-MATCH(A17,$A$2:$A$18,FALSE)
18	Mitchell	11/01/2015	="Day "&ROW()-MATCH(A18,$A$2:$A$18,FALSE)

<tbody>
</tbody>

 

[FDibbins]

Try this...
="Day "&COUNTIF($A$2:A2,A2)
copied down