# Formula for Unique Occurence using multiple columns

#### J2Reed

##### New Member
I am trying to achieved the desired results on E4. I'm looking for a formula that will count a unique occurrence specific to a date, ignoring duplicates and blanks.
In this case, 19NOV19 has 2 trucks. 20NOV19 has 3 trucks. 21NOV19 has 2 trucks.

#### James006

##### Well-known Member
Hi,

In cell E4, you can test following:
VBA Code:
``=SUMPRODUCT(((1/COUNTIF(B2:B5,B2:B5)*(A2:A5<>""))))+SUMPRODUCT(((1/COUNTIF(B8:B14,B8:B14)*(A8:A14<>""))))+SUMPRODUCT(((1/COUNTIF(B15:B20,B15:B20)*(A15:A20<>""))))``
Hope this will help

#### sandy666

##### Well-known Member
Just for fun with Power Query

1 2 3 4 DATE TRUCK Trucks Count 19/11/2019 FEDEX TRUCKS 7 19/11/2019 FEDEX 19/11/2019 FEDEX 19/11/2019 UPS 20/11/2019 FEDEX 20/11/2019 TRACY 20/11/2019 TRACY 20/11/2019 TRACY 20/11/2019 UPS 20/11/2019 UPS 20/11/2019 UPS 21/11/2019 FEDEX 21/11/2019 FEDEX 21/11/2019 FEDEX 21/11/2019 FEDEX 21/11/2019 TRACY 21/11/2019 TRACY

Code:
``````// Table10
let
Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
Filter = Table.SelectRows(Table.TransformColumnTypes(Source,{{"DATE", type date}, {"TRUCK", type text}}), each ([DATE] <> null)),
GrpTR = Table.Group(Filter, {"TRUCK"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}}),
GrpTRS = Table.Group(Table.AddColumn(GrpTR, "Trucks", each "TRUCKS"), {"Trucks"}, {{"Count", each List.Sum([Count]), type number}})
in
GrpTRS``````

#### J2Reed

##### New Member
Do I have to add a formula for each date? or can this be ongoing example: B2:B1000 A2:A1000, ignoring blanks.

#### Marcelo Branco

##### MrExcel MVP
Do I have to add a formula for each date? or can this be ongoing example: B2:B1000 A2:A1000, ignoring blanks.
Try this array formula
=SUM(IF(FREQUENCY(IF(A2:A1000<>"",IF(B2:B1000<>"",MATCH(A2:A1000&B2:B1000,A2:A1000&B2:B1000,0))),ROW(A2:A1000)-ROW(A2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

#### J2Reed

##### New Member
Thanks Marcelo, it works

Threads
1,077,677
Messages
5,335,616
Members
399,028
Latest member
greyland