Counting Duplicate Values Only Once in a Column B After Date & by Matching Criteria in Column A

[D_Nelson]

A little complex but I was looking for something to make it more simple for me to log trucks being parked at the yard.

In the example below, I look at Column A, find each vehicle (Say Truck 1) and then look at the corresponding value in Column B (Date #) and count it exactly once. Truck 1 has Sub Date # "10/15/2020" found in row 2, 4 and row 5 so I only count the occurrence of each individual Sub Date # exactly once. Truck 1 has Sub Date # 10/20/2020 occuring at row 8 and Sub # 10/27/2020 at row 11. The value returned for unique subscriptions associated with all the Truck 1 names is exactly 3 since we ignored the one occurrence of a duplicate (the Sub #1 ).

But then I would like to filter for those unique dates after 10/18/2020 (as an example)

Hopefully what I explained makes sense

The closest thing I found was in this article Counting Duplicate Values Only Once in a Column B by Matching Criteria in Column A

Which can filter individual dates by vehicle, but I'm not sure how to then make it happen after a particular date.
"Try

Array formula in E2 copied down
=SUM(IF(FREQUENCY(IF(A$2:A$12=D2,MATCH(B$2:B$12,B$2:B$12,0)),ROW(B$2:B$12)-ROW(B$2)+1),1))
Ctrl+Shift+Enter

If the column Sub# contains only numbers, not text, you can replace MATCH(B$2:B$12,B$2:B$12,0) by B$2:B$12"

A B C D E 1 Vehicles Date Name # Unique Date After Specific Date (e.g. 10/18/2020) 2 Truck 1 10/15/2020 Truck 1 2 3 Truck 2 10/15/2020 Truck 2 1 4 Truck 1 10/15/2020 Truck 3 3 5 Truck 1 10/15/2020 6 Truck 3 10/20/2020 7 Truck 2 10/20/2020 8 Truck 1 10/20/2020 9 Truck 2 10/20/2020 10 Truck 3 10/21/2020 11 Truck 1 10/27/2020 12 Truck 3 10/29/2020

 

[Domenic]

Try the following...

E2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(FREQUENCY(IF($A$2:$A$12=D2,IF($B$2:$B$12>DATE(2020,10,18),IF($B$2:$B$12<>"",MATCH($B$2:$B$12,$B$2:$B$12,0)))),ROW($B$2:$B$12)-ROW($B$2)+1)>0,1))

or

=SUM(IF(FREQUENCY(IF($A$2:$A$12=D2,IF($B$2:$B$12>DATE(2020,10,18),$B$2:$B$12)),IF($A$2:$A$12=D2,IF($B$2:$B$12>DATE(2020,10,18),$B$2:$B$12)))>0,1))

However, using dynamic array formulas...

E2, copied down:

=COUNT(UNIQUE(FILTER($B$2:$B$12,($A$2:$A$12=D2)*($B$2:$B$12>DATE(2020,10,18)))))

Hope this helps!

 

[D_Nelson]

This works perfectly thank you so much

 

[Domenic]

You're very welcome, cheers!

 

[D_Nelson]

You're very welcome, cheers!

Try the following...

E2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=SUM(IF(FREQUENCY(IF($A$2:$A$12=D2,IF($B$2:$B$12>DATE(2020,10,18),IF($B$2:$B$12<>"",MATCH($B$2:$B$12,$B$2:$B$12,0)))),ROW($B$2:$B$12)-ROW($B$2)+1)>0,1))

or

=SUM(IF(FREQUENCY(IF($A$2:$A$12=D2,IF($B$2:$B$12>DATE(2020,10,18),$B$2:$B$12)),IF($A$2:$A$12=D2,IF($B$2:$B$12>DATE(2020,10,18),$B$2:$B$12)))>0,1))

However, using dynamic array formulas...

E2, copied down:

=COUNT(UNIQUE(FILTER($B$2:$B$12,($A$2:$A$12=D2)*($B$2:$B$12>DATE(2020,10,18)))))

Hope this helps!

Just curious could something like this be applied in the header cells as an array for when the company expands and gets more trucks or when we outsource to other logistic companies rather than manually copying the formula?