Unique Values with condition

portucale

Board Regular
Joined
Jun 22, 2006
Messages
85
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,
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

portucale

Board Regular
Joined
Jun 22, 2006
Messages
85
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,099
Messages
5,622,696
Members
415,921
Latest member
ExcelGeek5038

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top