Formula to Count Unique ID numbers per month

[Tmoske]

I need to count how many unique ID numbers there are per month.I have figured out how to count the unique ID's for the first month (Jan) but after that I'm having trouble trying to get the count right for Feb and Mar. Formula I used in cell E2 is:
{=SUM(IF($A$2:$A$14<=DATE(2013,1,31),1/(COUNTIFS(A2:A14,"<="&DATE(2013,1,31),B2:B14,B2:B14)),0))}


-- removed inline image ---


Thanks,
Tmoske

 

[Tmoske]

Sorry, the image of my spreadsheet did not stay when I hi submit thread. How do I post my table?

 

[shyy]

How does your data look like? For the month is it simple June or 6/1/2014 etc..

 

[Tmoske]

Dates are written out like 6/1/2014

 

[Marcelo Branco]

Maybe something like this (dates as dd/mm/yyyy)


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]

Date​

[/td][td]

ID​

[/td][td] [/td][td]

Month​

[/td][td]

Year​

[/td][td]

Unique​

[/td][/tr]
[tr][td]

2​

[/td][td]

25/01/2014​

[/td][td]

ID1​

[/td][td] [/td][td]

1​

[/td][td]

2014​

[/td][td]

4​

[/td][/tr]
[tr][td]

3​

[/td][td]

26/01/2014​

[/td][td]

ID2​

[/td][td] [/td][td]

2​

[/td][td]

2014​

[/td][td]

2​

[/td][/tr]
[tr][td]

4​

[/td][td]

27/01/2014​

[/td][td]

ID3​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

5​

[/td][td]

28/01/2014​

[/td][td]

ID4​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

6​

[/td][td]

29/01/2014​

[/td][td]

ID2​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

7​

[/td][td]

30/01/2014​

[/td][td]

ID1​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

8​

[/td][td]

31/01/2014​

[/td][td]

ID3​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

9​

[/td][td]

01/02/2014​

[/td][td]

ID1​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

10​

[/td][td]

02/02/2014​

[/td][td]

ID4​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

11​

[/td][td]

03/02/2014​

[/td][td]

ID1​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

12​

[/td][td]

04/02/2014​

[/td][td]

ID4​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

13​

[/td][td]

05/02/2014​

[/td][td]

ID4​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

14​

[/td][td]

06/02/2014​

[/td][td]

ID4​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]

Array formula in F2 copied down
=SUM(IF(FREQUENCY(IF($A$2:$A$14<>"",IF(MONTH($A$2:$A$14)=D2,IF(YEAR($A$2:$A$14)=E2,MATCH($B$2:$B$14,$B$2:$B$14,0)))),ROW($B$2:$B$14)-ROW($B$2)+1),1))

confirmed with Ctrl+Shift+Enter

M.

 

[Tmoske]

If it matters my ID numbers are alpha numeric. Example BAF13033M01.

 

[Tmoske]

Marcelo,
Thanks so much, the formula seems to work exactly the way that I needed.

-Tmoske

 

[Marcelo Branco]

Marcelo,
Thanks so much, the formula seems to work exactly the way that I needed.

-Tmoske

You are welcome. Glad for helping

M.