Ron Coderre wrote...
Regarding:
SUMPRODUCT((A2:A8=E2)/COUNTIF(C2:C8,C2:C8))

Possible issue if the same Bill of Lading ref could exist for more
than one shipper. Not enough information to determine if there's
any exposure on that, though.
....

It gets more complicated and requires a longer formula.

=SUM(IF(A2:A8=E2,1/MMULT(TRANSPOSE(A2:A8=E2)
*(C2:C8=TRANSPOSE(C2:C8)),ROW(C2:C8)^0)))

The advantage of this formula is that it works when col C values could
contain wildcards. But maybe overkill. My main objection to Biff's
formula was multiple MATCH calls, but they could be reduced.

=COUNT(1/FREQUENCY(IF(A2:A8=E2,MATCH(C2:C8,C2:C8,0)),
ROW(C2:C8)-MIN(ROW(C2:C8))+1))