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

#### D_Nelson

##### New Member
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"

ABCDE
1VehiclesDateName# Unique Date After Specific Date (e.g. 10/18/2020)
2Truck 110/15/2020Truck 12
3Truck 210/15/2020Truck 21
4Truck 110/15/2020Truck 33
5Truck 110/15/2020
6Truck 310/20/2020
7Truck 210/20/2020
8Truck 110/20/2020
9Truck 210/20/2020
10Truck 310/21/2020
11Truck 110/27/2020
12Truck 310/29/2020

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Domenic

##### MrExcel MVP
Try the following...

VBA Code:
``````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...

VBA Code:
``````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

##### New Member
This works perfectly thank you so much

#### Domenic

##### MrExcel MVP
You're very welcome, cheers!

#### D_Nelson

##### New Member
You're very welcome, cheers!
Try the following...

VBA Code:
``````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...

VBA Code:
``````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?

Last edited by a moderator:

Replies
1
Views
98
Replies
1
Views
203
Replies
11
Views
159
Replies
1
Views
78
Replies
1
Views
91

1,130,315
Messages
5,641,482
Members
417,210
Latest member
rins

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back