Count unique lines

Chants

New Member
Joined
Oct 16, 2006
Messages
7
Please can someone help.

I have two columns; A=User id's and B=date.

I would like to know how many user id's in each month, but the user id's are not unique. How do i only count the unique user id's?
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows
Hi

Welcome to the board! One of many options:

In C2 (assume you start from row 2 with labels above?):
=COUNTIF($A$2:A2,A2)
copy and paste this down to end of range.

Then to determine how many unqiue ID's in a month:
=SUMPRODUCT(--(B1:B100>=#date#),--(B1:B100)<=#date#),--(C1:C100<2))

where #date# is iether the actual date types in, or linked to a cell if you prefer. This formula looks between dates so for one month date1 may be 01/01/06 and date2 will be 31/01/06.

If you want date specific then simply use --(B1:B100=#date#) and take out the other date conditions.

Hope this helps.

Jon :)
 

Watch MrExcel Video

Forum statistics

Threads
1,112,885
Messages
5,543,038
Members
410,583
Latest member
gazz57
Top