How to Count cells removing duplicate cells on certain date

[mvkakaria]

Hi All,

Suppose We have the above data for example, I want function for counting cells in O column on particular date. Say on 25th Aug 2022 total count in O column removing duplicates comes to 3. I want function for the same to be used in Excel.

Thankyou.

 

[JEC]

Try this matrix formula (close with ctrl + shift + return).
Note that this formule makes your workbook slow with more data

=SUMPRODUCT(IFERROR(1/COUNTIFS(N1:N12,$N$1,O1:O12,O1:O12),0))

 

[JEC]

Here also a UDF

Function jec(rng As Range, strDate As Long) As Long
 Dim it
 With CreateObject("scripting.dictionary")
   For Each it In rng
     If it.Offset(, -1).Value = strDate Then .Item(it.Value) = Empty
   Next
   jec = .Count
 End With
End Function

ZOEKEN IN 3 KOLOMMEN (1).xlsm
	N	O	P	Q	R
1	25-8-2022	a		UDF	Formula
2	25-8-2022	b		3	3
3	25-8-2022	c
4	25-8-2022	a
5	25-8-2022	a
6	26-8-2022	d
7	26-8-2022	d
8	26-8-2022	d
9	26-8-2022	d
10	26-8-2022	e
11	26-8-2022	e
12	26-8-2022	f
Blad2

Cell Formulas
Range	Formula
Q2	Q2	=jec(O1:O12,N1)
R2	R2	=SUMPRODUCT(IFERROR(1/COUNTIFS(N1:N12,$N$1,O1:O12,O1:O12),0))

 

[mvkakaria]

Can you please share the excel file template of the above.

Thankyou

 

[JEC]

This forum has no file sharing functionality. You just need to copy the formula. Enter the formula with ctrl+shft+return

 

[mvkakaria]

Here also a UDF

Function jec(rng As Range, strDate As Long) As Long
 Dim it
 With CreateObject("scripting.dictionary")
   For Each it In rng
     If it.Offset(, -1).Value = strDate Then .Item(it.Value) = Empty
   Next
   jec = .Count
 End With
End Function

ZOEKEN IN 3 KOLOMMEN (1).xlsm
	N	O	P	Q	R
1	25-8-2022	a		UDF	Formula
2	25-8-2022	b		3	3
3	25-8-2022	c
4	25-8-2022	a
5	25-8-2022	a
6	26-8-2022	d
7	26-8-2022	d
8	26-8-2022	d
9	26-8-2022	d
10	26-8-2022	e
11	26-8-2022	e
12	26-8-2022	f
Blad2

Cell Formulas
Range	Formula
Q2	Q2	=jec(O1:O12,N1)
R2	R2	=SUMPRODUCT(IFERROR(1/COUNTIFS(N1:N12,$N$1,O1:O12,O1:O12),0))

Thanyou so much, solved the problem.