Count only unique values

[cunningAce]

Hi,

In the example below I want to be able to enter the date and have the formula return the number of unique ID's related to it.
So the result would be 9 in this case.

Thanks in advance for your help

	A	B	C	D	E
1
1
1
2
2
2
3
4
5
6
6
7
7
7
8
8
9
9
9

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]Date[/TD]
[TD="bgcolor: #FFF2CC"]ID[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]Date[/TD]
[TD="bgcolor: #FFF2CC"]Count[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"]18/06/2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]18/06/2018[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

​

 

[Legacy 421546]

First count all, then delete the duplicates, then count the remaining

 

[VBA Geek]

Excel 2013/2016
	A	B	C	D	E
1	Date	ID		Date	Count
2	6/18/2018	1		6/18/2018	9
3	6/18/2018	1
4	6/18/2018	1
5	6/18/2018	2
6	6/18/2018	2
7	6/18/2018	2
8	6/18/2018	3
9	6/18/2018	4
10	6/18/2018	5
11	6/18/2018	6
12	6/18/2018	6
13	6/18/2018	7
14	6/18/2018	7
15	6/18/2018	7
16	6/18/2018	8
17	6/18/2018	8
18	6/18/2018	9
19	6/18/2018	9
20	6/18/2018	9
Sheet3

Cell Formulas
Range	Formula
E2	=SUMPRODUCT(1/COUNTIFS(B2:B20,B2:B20,A2:A20,A2:A20),N(A2:A20=D2))

 

[Aladin Akyurek]

In E2 enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$20=D2,$B$2:$B$20),$B$2:$B$20),1))

which is fast.

 

[cunningAce]

Hi,

Thanks for your response,
This works for the example but when I add to my data with new dates and IDs it doesn't work. Can help to amend?

Book1
	A	B	C	D	E
1	Date	ID		Date	Count
2	18/06/2018	1		19/06/2018	#VALUE!
3	18/06/2018	1
4	18/06/2018	1
5	18/06/2018	2
6	18/06/2018	2
7	18/06/2018	2
8	18/06/2018	3
9	18/06/2018	4
10	18/06/2018	5
11	18/06/2018	6
12	19/06/2018	1
13	19/06/2018	2
14	19/06/2018	3
15	19/06/2018	4
16	19/06/2018	5
17	19/06/2018	6
18	19/06/2018	7
19	19/06/2018	8
20	19/06/2018	9
Sheet1

Cell Formulas
Range	Formula
E2	=SUM(IF(FREQUENCY(IF($A$2:$A$20=D2,$B$2:$B$20),$B$2:$B$20),1))

 

[cunningAce]

Hi,

Thanks for your help. This works. The only downfall is my data is quite large and when I change the cell references to complete rows the calculation is incredibly slow

 

[Aladin Akyurek]

Hi,

Thanks for your help. This works. The only downfall is my data is quite large and when I change the cell references to complete rows the calculation is incredibly slow

Care to post the formula as you implement it?

 

[cunningAce]

Care to post the formula as you implement it?

I am using the Frequency formula that you suggested. I had replied to the other suggestion using product regarding calculation time.

I prefer the Frequency formula as its quick but doesn't work when I add dates and Ids to the data.

 

[Aladin Akyurek]

Hi,

Thanks for your response,
This works for the example but when I add to my data with new dates and IDs it doesn't work. Can help to amend?

Book1
	A	B	C	D	E
1	Date	ID		Date	Count
2	18/06/2018	1		19/06/2018	#VALUE!
3	18/06/2018	1
4	18/06/2018	1
5	18/06/2018	2
6	18/06/2018	2
7	18/06/2018	2
8	18/06/2018	3
9	18/06/2018	4
10	18/06/2018	5
11	18/06/2018	6
12	19/06/2018	1
13	19/06/2018	2
14	19/06/2018	3
15	19/06/2018	4
16	19/06/2018	5
17	19/06/2018	6
18	19/06/2018	7
19	19/06/2018	8
20	19/06/2018	9
Sheet1

Cell Formulas
Range	Formula
E2	=SUM(IF(FREQUENCY(IF($A$2:$A$20=D2,$B$2:$B$20),$B$2:$B$20),1))

I am using the Frequency formula that you suggested. I had replied to the other suggestion using product regarding calculation time.

I prefer the Frequency formula as its quick but doesn't work when I add dates and Ids to the data.

You need to confirm the formula with control+shift+enter, not just with enter. I have apparently forgotten to mention that.