How to get the unique record from the combination?

[kanigelpula]

Hi team,

I have three columns in my spread sheet ( call nuumnber, Month and the cost).

I need to find out

1) The unique call number for the month, to calculate the metric in step (2).

For eg, the call number P4DKT57 is spread across two month so, it is needs to take unique count in first and unique in the later months. So, the following should be the kind of o/p I am after
P4DKT57 Jul-11 1
P4DKT57 Feb-11 1


2) Create a pivot chart that illustrates the CPC per month,
CpC is calculated by dividing the sum of all costs by the total number of unique call number.

Sample Data Table:

Call Number Month Cost
P4DHF23 Jul-11 $58.17
P4DZH1D Jul-11 $58.17
P4DZH1D Jul-11 $48.17
P4DKT57 Jul-11 $58.17
P4DKT57 Jul-11 $78.17
P4DKT57 Feb-11 $58.17
P4DZ6KL Jul-11 $99.17
P4DZ6KL Mar-11 $58.17
1789018Z Jul-11 $58.17
1789018Z Apr-11 $68.17


How is this avheivable in excel 2007.

The sample spread sheet is attached. Could you please help me with this.

Thanks.