"Count" specific field

[Deepak]

Hi all

Please help me to sort out the following Problem:

I've a list in excel with headers as:

Telephone No. Name Recharge Date Count
12345678 abcd 06/01/2004 1
2345678 abdeed 06/04/2004 1
12345678 abcd 06/07/2004 1
12345678 abcd 06/14/2004 1
25269853 klmn 06/15/2004 1


As you can see "Count" field is always 1, "Telephone No." have duplicate
values but different "Recharge Date".

Now, I want a formula that gives the following result:

Telephone No. Name Count
12345678 abcd 3

That is, i want number of times a specific telephone number is being
"recharged"


Kindly suggest and help

Thanks
Deepak

 

[Deepak]

To add to it:

Please let me know if i can get the maximum of "Recharge Date" for a
particular "Telephone Number"

 

[Norman Harker]

Hi Deepak!

Assuming that your name data is in the range B2:B50

Use:

=COUNTIF($B$2:$B$50,"abcd")

 

[Norman Harker]

Hi Deepak!

You need an array entered formula:

=MAX(IF($B$2:$B$50="abcd",$C$2:$C$50))
Entered by pressing and holding down Ctrl + Shift and then pressing
Enter. Appears in the formula bar as:
Format as a date

{=MAX(IF($B$2:$B$50="abcd",$C$2:$C$50))}

 

[Alan Beban]

=SUMIF(A1:A6,12345678,D16)

Alan Beban

 

[Alan Beban]

=INDEX(C2:C8,MATCH(MAX(IF(A2:A8="James",B2:B8)),B2:B8,0))

Alan Beban