• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to count distinct values with multi colum

kailashxl

New Member
I have the excel data in Column A and B respectively below. I want to compute the distinct count of 'code' where startdate is between (22-aug-2022 and 28-aug-2022). Your help would be appreciated. Thanks in advance

Code Startdate
------ -------------
123 21-aug-2022
124 25-aug-2022
124 25-aug-2022
127 26-aug-2022
128 24-aug-2022
128 24-aug-2022
 
=SUMPRODUCT(IF((B2:B8<=E2)*(B2:B8>=E1), 1/COUNTIFS(B2:B8, "<="&E2, B2:B8, ">="&E1, A2:A8, A2:A8), 0))

where E1 is start date and E2 end date
 

Attachments

  • CountUnique.xlsx
    9.7 KB · Views: 4
=SUMPRODUCT(IF((B2:B8<=E2)*(B2:B8>=E1), 1/COUNTIFS(B2:B8, "<="&E2, B2:B8, ">="&E1, A2:A8, A2:A8), 0))

where E1 is start date and E2 end date
Thanks for your reply. Can I use range specs for A and B as A:A and B:B resp assuming the data is changing at random?
 
Office 365/Excel2019:
In @ETAF 's file
=COUNT(UNIQUE(FILTER(A3:A8,(B3:B8>=E1)*(B3:B8<=E2))))
and re:
Can I use range specs for A and B as A:A and B:B
Yes, but I wouldn't guarantee its results if there's other stuff outside the intended range:
=COUNT(UNIQUE(FILTER(A:A,(B:B>=E1)*(B:B<=E2))))

Consider making your range into a table leaving:
Code:
=COUNT(UNIQUE(FILTER(Table1[Code],(Table1[Startdate]>=E1)*(Table1[Startdate]<=E2))))
or shorter:

Code:
=LET(a,Table1[Startdate],COUNT(UNIQUE(FILTER(Table1[Code],a>=E1)*(a<=E2))))
 
Last edited:
sumproduct does not lile full column reference
personally nor do I -some argument on time to calculate million of rows
365 has 1,048,576 rows
so i tend to use , A2:A100000 - if largish or what ever row i think will be the maximum of data
but p45cal will work
=COUNT(UNIQUE(FILTER(A:A,(B:B>=E1)*(B:B<=E2))))
 

Attachments

  • CountUnique-V2.xlsx
    9.9 KB · Views: 2
A slightly different mindset. Like @ETAF , I dislike whole column references but I take things further and fit Names exactly to content by using structed references (input data) or dynamic arrays (calculated data). My formula was
Code:
= COUNT(
    UNIQUE(
      FILTER(Code,IsBetweenλ(Startdate,start,end))
    )
  )
but that hides the definition of the Lambda function IsBetweenλ
Code:
IsBetweenλ
= LAMBDA(date, s, e,
     MAP(date, LAMBDA(d, AND(d >= s, d <= e)))
  );
 
Code
= Table1[Code];
Startdate
= Table1[Startdate];
 

Attachments

  • CountUnique-V2.xlsx
    14.7 KB · Views: 4
An alternative is with Power Query

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Top Rows" = Table.Skip(Source,1),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Startdate", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Code", "Startdate"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
    #"Grouped Rows"

Excel 2016 (Windows) 64 bit
A
B
C
D
E
F
1
CodeStartdateCodeStartdateCount
2
-------------------
123​
8/21/2022​
1​
3
123​
21-Aug-22​
124​
8/25/2022​
1​
4
124​
25-Aug-22​
127​
8/26/2022​
1​
5
124​
25-Aug-22​
128​
8/24/2022​
1​
6
127​
26-Aug-22​
7
128​
24-Aug-22​
8
128​
24-Aug-22​
Sheet: Sheet1
 
Select the range of cells, or make sure the active cell is in a table.

Make sure the range of cells has a column heading.

On the Data tab, in the Sort & Filter group, click Advanced.

The Advanced Filter dialog box appears.

Click Copy to another location.

In the Copy to box, enter a cell reference.

Alternatively, click Collapse Dialog Button image to temporarily hide the dialog box, select a cell on the worksheet, and then press Expand Dialog Button image.

Select the Unique records only check box, and click OK.

The unique values from the selected range are copied to the new location beginning with the cell you specified in the Copy to box.

In the blank cell below the last cell in the range, enter the ROWS function. Use the range of unique values that you just copied as the argument, excluding the column heading. For example, if the range of unique values is B2:B45, you enter =ROWS(B2:B45).

Regards,
Peter
 
Back
Top