# 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

#### ETAF

##### Member
=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

• 9.7 KB Views: 3

#### kailashxl

##### New Member
=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?

#### p45cal

##### Well-Known Member
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:

#### ETAF

##### Member
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

• 9.9 KB Views: 2

#### kailashxl

##### New Member
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?
The above is array formula which worked. Appreciate your help!

#### Peter Bartholomew

##### Well-Known Member
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

• 14.7 KB Views: 4

#### AlanSidman

##### Well-Known Member
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

#### petergroft

##### New Member
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