Formula for Unique Occurence using multiple columns

J2Reed

New Member
Joined
Nov 12, 2018
Messages
14
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.
EXCEL PIC.PNG
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,669
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
Joined
Oct 24, 2015
Messages
3,292
Just for fun with Power Query

1DATETRUCKTrucksCount
219/11/2019FEDEXTRUCKS7
319/11/2019FEDEX
419/11/2019FEDEX
519/11/2019UPS
6
7
820/11/2019FEDEX
920/11/2019TRACY
1020/11/2019TRACY
1120/11/2019TRACY
1220/11/2019UPS
1320/11/2019UPS
1420/11/2019UPS
1521/11/2019FEDEX
1621/11/2019FEDEX
1721/11/2019FEDEX
1821/11/2019FEDEX
1921/11/2019TRACY
2021/11/2019TRACY


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
Joined
Nov 12, 2018
Messages
14
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
Joined
Aug 23, 2010
Messages
16,305
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.
 

Forum statistics

Threads
1,078,283
Messages
5,339,281
Members
399,291
Latest member
Bdbd55

Some videos you may like

This Week's Hot Topics

Top