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

 

[razzandy]

Thanks its working now but with incorrect results!

I changed the formula to =SUMPRODUCT(--(ShpGR!L1:L9999=A4)) this gives me 3 as the result but as you will see from the screen shot below it should read 2 becuse the consigment number selected on the data sheet is XX010101 and there are 2 differant orders (SC#) shipped!?

System link WORK.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	SC#	Item#	Part#	Description	QtyShipped	Shipment#	BL#	ETD	ETA	AOA	Agent	SupplierInv#
2	88	1	DF90SR80	FlangedShortRadBend(9.6kg)	10	1	BLTEST	01/01/2001	31/01/2001	01/01/2001	SHANXI	XX010101
3	88	3	AFT80X80	AllFlangedTee(15.6kg)	4	1	BLTEST	01/01/2001	31/01/2001	01/01/2001	SHANXI	XX010101
4	89	4	AFT100X100	AllFlangedTee(19.4kg)	7	1	BLTEST	01/01/2001	31/01/2001	01/01/2001	SHANXI	XX010101
ShpGR

Cheers

 

[GlennUK]

Oh dear, much more complicated

I didn't realise there'd be multiple entries in column A that you wanted counting as 1 ... but, here's a formula:

=SUM(IF(ShpGR!L2:L13=A4,ROW(ShpGR!2:13)/ROW(ShpGR!2:13),0)/IF(ShpGR!L2:L13=A4,COUNTIF(ShpGR!A2:A13,ShpGR!A2:A13),1))

entered using Ctrl-Shift-Enter instead of Enter.

Adjust all occurences of L2:L13, 2:13, and A2:A13 to cover the row range for your data.

 

[Domenic]

Here's another way...

=SUMPRODUCT((ShpGR!L2:L9999=A4)/COUNTIF(ShpGR!A2:A9999,ShpGR!A2:A9999&""))

Hope this helps!

 

[GlennUK]

Well done Domenic, my brain was just about fried ... so blurted out that complex IF one instead of a neat one like yours.

 

[razzandy]

Thank you both very much for all your help. I’ve tried both formulas:

GlennUK: Your formula returns 1.5 in sum instances but runs very smoothly

Domenic: You code works perfect but sends my system in to melt down. It slooooooooooooooows it right down.


If GlennUK’s code would be more accurate I think I’d be pulling myself off

Thought this may have got everybody’s head hurting :x

PS Just a thought could DPRODUCT not be used?

Many Many Thanks

Ryan A UK

 

[Domenic]

Try the following...

N2, copied down:

=IF(ISNA(MATCH(A2,$A$1:A1,0)),COUNTIF($A$2:$A$9999,A2),0)

Then use the following formula...

=SUMPRODUCT(--(L2:L9999=P2),--(N2:N9999>0))

...where P2 contains your 'consignment number' of interest, such as XX010101. If Column L is sorted in ascending order, the last formula can be replaced with the following...

Q2:

=MATCH(P2,L2:L9999,0)

R2:

=MATCH(P2,L2:L9999)

S2:

=SUMPRODUCT(--(OFFSET(L2,Q2-1,0,R2-Q2+1)=P2),--(OFFSET(N2,Q2-1,0,R2-Q2+1)>0))

Hope this helps!