FREQUENCY Formula help ?

razzandy

Active Member
Joined
Jun 26, 2002
Messages
390
Office Version
  1. 2007
Platform
  1. Windows
Hi :biggrin:

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 :oops:
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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.
 
Upvote 0
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 :biggrin:

Ryan A UK
 
Upvote 0
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.
 
Upvote 0
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 :oops:

Cheers

Ryan A UK
 
Upvote 0
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?
 
Upvote 0
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." :oops:

Cheers

Ryan A UK[/img]
 
Upvote 0
Please paste your attempt at the formula. Can you confirm the existance of the ShpGR sheet? What cell did you put the formula in?
 
Upvote 0
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! :cry:

Cheers
 
Upvote 0
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 ).
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top