Count Unique IDs for each Date

[erenkey]

I have a spreadsheet that contains customer IDs in Column A and Dates in Column B. Each customer can have the same date more than once. I am trying to count the unique IDs for each date. I tried using the following formula but it did not work:

=SUMPRODUCT(--($B$2:$B$59000=D$3),--(SUM(IF(FREQUENCY($A$2:$A$59000,$A$2:$A$59000)>0,1))))

Where Column A is the list of IDs
Column B is the list of Dates
Column D is the date I want to count.

Any ideas?

 

[T. Valko]

I have a spreadsheet that contains customer IDs in Column A and Dates in Column B. Each customer can have the same date more than once. I am trying to count the unique IDs for each date. I tried using the following formula but it did not work:

=SUMPRODUCT(--($B$2:$B$59000=D$3),--(SUM(IF(FREQUENCY($A$2:$A$59000,$A$2:$A$59000)>0,1))))

Where Column A is the list of IDs
Column B is the list of Dates
Column D is the date I want to count.

Any ideas?

With that many rows of data this will be very slow to calculate...

Array entered**:

=SUM(IF(FREQUENCY(IF(B2:B59000=D3,MATCH(A2:A59000,A2:A59000,0)),ROW(A2:A59000)-ROW(A2)+1),1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Assumes no empty cells within the data range.

 

[erenkey]

That works perfectly.

Now can I add something to it? What if I wanted to count how many IDs appear one time, two times or three times? An ID can only appear up to three times per date.

 

[T. Valko]

That works perfectly.

Now can I add something to it? What if I wanted to count how many IDs appear one time, two times or three times? An ID can only appear up to three times per date.

If I understand what you want...

Just add the number like this:

=SUM(IF(FREQUENCY(IF(B2:B59000=D3,MATCH(A2:A59000,A2:A59000,0)),ROW(A2:A59000)-ROW(A2)+1)=n,1))

Where n = 1, 2, 3, whatever

 

[erenkey]

That is exactly what I wanted. Thanks for your help.

 

[T. Valko]

That is exactly what I wanted. Thanks for your help.

You're welcome. Thanks for the feedback!