Countif only on duplicate values within date range.

Given nature of calc I would, personally, advocate the use of intermediate calc on your result tab - as you indicate you're open to...

So, for speed, I would be inclined to do something like:

Formula:

Please Login or Register  to view this content.

if you need to build in some excess capacity embed some pre-emptive checks to the above to handle blanks, and ensure you return 0 (in Col B)

in short the above is designed to both:
a) use COUNTIF rather than COUNTIFS (so storing "key" in Col A as string thereby permitting solitary wildcard count), and
b) to only enact the potentially expensive AGGREGATE call when necessary (i.e. potential duplicate)

the above would return 4 (in total)

a single cell calc of the same would be quite expensive, computationally speaking -- and a UDF would probably be more elegant / efficient.

edit:

final note - if the values in D are always numeric in nature, and < 16 digits, you could use this in preference to the MATCH piece in Col A of the key, which would be quicker still:

Formula:

Please Login or Register  to view this content.