Count unique lines

[Chants]

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?

 

[Jon von der Heyden]

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

 

[SteveO59L]

Try a pivot table

 

[Chants]

Perfect! Did the first option.

Thanks a mil Jon!