Formula for Unique Occurence using multiple columns

[J2Reed]

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]

Hi,

In cell E4, you can test following:

=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]

Just for fun with Power Query

	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

// 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]

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

 

[Marcelo Branco]

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]

Thanks Marcelo, it works