FREQUENCY Formula help ?

[razzandy]

Hi

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

 

[BrianB]

I wonder if a pivot table would work.
It looks like you could use Consignment as a page field, when you could select individual consignments and see which orders make them up - or just use Count of Orders as the data field.

 

[razzandy]

Thanks BrianB

No that’s no good for me simply because the info is being read/used by a userform! Maybe its' simpler to use code?

Cheers

Ryan A UK

 

[GlennUK]

If you really only need how many different orders are being shipped for a given consignment number, then:

=SUMPRODUCT(--(ShpGR!A1:9999=ShpGR!L2))

where it's counting entries for the consignment as in cell L2.

 

[razzandy]

Thanks GlennUK

Does this work for you I can't seem to get it to work even with the help files?

Where should I put this formula?

I just cant make head na tale of it

Cheers

Ryan A UK

 

[GlennUK]

Put it the same place as your first attempt at a formula ( a cell???? ). Are you using the exact same formula as I posted? What result does it give?

 

[razzandy]

Hi

Yea exactly as you typed it. I just copied and pasted it over my first attempt. As soon as I hit the enter key it says "The formula you have typed contains an error."

Cheers

Ryan A UK[/img]

 

[GlennUK]

Please paste your attempt at the formula. Can you confirm the existance of the ShpGR sheet? What cell did you put the formula in?

 

[razzandy]

Hi

The formula is on a Sheet called "Data" A40 and the cell which I probably should have told you about before, which has the consignment number I wish to lookis in (cell A4). The database is on the "ShpGR" sheet.

Sorry for being a bit thick!

Cheers

 

[GlennUK]

So are you trying to use:

=SUMPRODUCT(--(ShpGR!A1:A9999=A4))

Please try again ( my formula had a slight typo that actually still worked in Excel 97 ).