Unique Values with condition

[portucale]

Hi,

I am in a muddle as I need to ascertain the hits in a web page by users, I did managed to get the sum of hits by month, but I also need to ascertain the number of Unique Users by month. I can ascertain them in one month but as I do have a list of about one year I was looking for a way that I could ascertain the Unique records by month.

Below is a sample data:
Table......................................| Results ...........................................
Month Date UserID Apr-12 May-12
Apr-12 02-Apr-12 aah97 9 6 unique values
Apr-12 02-Apr-12 aan93
Apr-12 02-Apr-12 abl59
Apr-12 02-Apr-12 abr03
Apr-12 02-Apr-12 aan93
Apr-12 03-Apr-12 abu48
Apr-12 07-Apr-12 wwa26
Apr-12 07-Apr-12 yab86
Apr-12 07-Apr-12 aan93
Apr-12 07-Apr-12 ymo20
Apr-12 07-Apr-12 ysm47
May-12 01-May-12 aah97
May-12 01-May-12 aan93
May-12 01-May-12 abl59
May-12 01-May-12 aah97
May-12 01-May-12 abr22
May-12 02-May-12 abu48
May-12 02-May-12 aah97
May-12 02-May-12 agr34
May-12 02-May-12 aah97

Apreciate any help.

Thanks,

 

[Domski]

Using a pivot table: http://www.contextures.com/xlPivot07.html#Unique

Dom

 

[portucale]

Hi,

Thanks tried that and seems it works, however I come up with a formula which also seems to work...

{=SUM(N(FREQUENCY(IF(($A$29:$A$48=F29)*($D$29:$D$48=G29),MATCH($C$29:$C$48,$C$29:$C$48,)),MATCH($C$29:$C$48,$C$29:$C$48,))>0))}

Where Column A is Months, column D is Locations and column C are the users, this formula gives me the unique Users per Location and per month.

Many Thanks for the help