# FREQUENCY Formula help ?

#### razzandy

##### Active Member
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?

Ryan A UK

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### BrianB

##### Well-known Member
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

##### Active Member
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

##### Well-known Member
If you really only need how many different orders are being shipped for a given consignment number, then:
Code:
``=SUMPRODUCT(--(ShpGR!A1:9999=ShpGR!L2))``

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

#### razzandy

##### Active Member
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

##### Well-known Member
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

##### Active Member
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

##### Well-known Member
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

##### Active Member
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

##### Well-known Member
So are you trying to use:
Code:
``=SUMPRODUCT(--(ShpGR!A1:A9999=A4))``

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

Replies
6
Views
237
Replies
1
Views
94
Replies
1
Views
152
Replies
7
Views
138
Replies
2
Views
192

1,195,962
Messages
6,012,585
Members
441,713
Latest member
Dave353

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back