Count repeat occurance, with duplicate values

Please Login or Register  to view this content.

Hi,

Above is a small piece of a table I'm working with. The column "CNUM" is 'Customer Number'.. Column "BL" is 'Bill of Lading Number'.. I'm trying to count how many unique BL numbers belong to the corresponding CNUM. As you can see, the CNUM '4882' has ordered 12 line items as 6 different orders. The value i want it to spit out in 'REPEATS' column is 6.

I've googled a bit and found similar questions, solutions found with COUNTIF / COUNTIFS and also SUMPRODUCT.. But i haven't gotten it yet after some playing. Any help?

This array formula** is more efficient...

=SUM(IF(FREQUENCY(IF(A3:A14=A3,F3:F14),F3:F14),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.