I have a database which records shipment/consignments.

In column ‘L’ I have the consignment numbers and in column ‘A’ I have the order numbers. What happens is we have a shipments made up of several order numbers so what I’m trying to do is find out how many different orders are being shipped for a given consignment number. I’m using the below formula but this finds all of them for all consignments not just one specific one.

I can get in working with dynamic named ranges but I want to a void dynamic ranges if possible!

=SUM(IF(FREQUENCY(ShpGR!A:A,ShpGR!A:A)>0,1))

Any ideas?

Thanks in advance

Ryan A UK