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,659
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,207
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,296
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,077,677
Messages
5,335,616
Members
399,028
Latest member
greyland

Some videos you may like

This Week's Hot Topics

Top