I have a database of containers, example as follows:

Column A Column B Column C Column D
Port Load Port Unload Cntr Size Container No
ANT MTL 20 AA123456
ANT MTL 20 AA123456
ANT MTL 20 BC109876
HAM LEH 40 DD294567

I have a formula which counts the number of containers
={COUNT(IF(A1:A100="ANT",IF(B1:B100="MTL",IF(C1:C1 00=20,D1:D100))))}
when entered as an array counts the containers I want, but my problem is that
I want to exclude duplicated containers in column D.

Any suggestions as to how I could achieve this?

--
Message posted via http://www.officekb.com

You can try this *array* formula:

=COUNT(1/FREQUENCY(IF((INDEX(A1:D100,,1)=E1)*(INDEX(A1:D100 ,,2)=E2)*(INDEX(A
1:D100,,3)=E3),MATCH(INDEX(A1:D100,,4),INDEX(A1:D1 00,,4),0)+CELL("Row",A1:D1
00)),ROW(A1:D100)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"GRM via OfficeKB.com" <u14934@uwe wrote in message
news:575aa287bf325@uwe...
I have a database of containers, example as follows:

Column A Column B Column C Column D
Port Load Port Unload Cntr Size Container No
ANT MTL 20 AA123456
ANT MTL 20 AA123456
ANT MTL 20 BC109876
HAM LEH 40 DD294567

I have a formula which counts the number of containers
={COUNT(IF(A1:A100="ANT",IF(B1:B100="MTL",IF(C1:C1 00=20,D1:D100))))}
when entered as an array counts the containers I want, but my problem is
that
I want to exclude duplicated containers in column D.

Any suggestions as to how I could achieve this?

--
Message posted via http://www.officekb.com

Forgot to mention that your criteria must be entered in:

E1 = Port Load
E2 = Port Unload
E3 = Cont. Size

Makes it easier to change the criteria, instead of going into the formula.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"RagDyeR" wrote in message
...
You can try this *array* formula:

=COUNT(1/FREQUENCY(IF((INDEX(A1:D100,,1)=E1)*(INDEX(A1:D100 ,,2)=E2)*(INDEX(A
1:D100,,3)=E3),MATCH(INDEX(A1:D100,,4),INDEX(A1:D1 00,,4),0)+CELL("Row",A1:D1
00)),ROW(A1:D100)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"GRM via OfficeKB.com" <u14934@uwe wrote in message
news:575aa287bf325@uwe...
I have a database of containers, example as follows:

Column A Column B Column C Column D
Port Load Port Unload Cntr Size Container No
ANT MTL 20 AA123456
ANT MTL 20 AA123456
ANT MTL 20 BC109876
HAM LEH 40 DD294567

I have a formula which counts the number of containers
={COUNT(IF(A1:A100="ANT",IF(B1:B100="MTL",IF(C1:C1 00=20,D1:D100))))}
when entered as an array counts the containers I want, but my problem is
that
I want to exclude duplicated containers in column D.

Any suggestions as to how I could achieve this?

--
Message posted via http://www.officekb.com

Try this *non=array* formula:

=SUMPRODUCT((A1:A100=E1)*(B1:B100=E2)*(C1:C100=E3) *(D1:D100<"")/COUNTIF(D1:
D100,D1:D100&""))

Same stipulations about entering criteria in E1, E2, and E3.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"RagDyeR" wrote in message
...
Forgot to mention that your criteria must be entered in:

E1 = Port Load
E2 = Port Unload
E3 = Cont. Size

Makes it easier to change the criteria, instead of going into the formula.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"RagDyeR" wrote in message
...
You can try this *array* formula:

=COUNT(1/FREQUENCY(IF((INDEX(A1:D100,,1)=E1)*(INDEX(A1:D100 ,,2)=E2)*(INDEX(A
1:D100,,3)=E3),MATCH(INDEX(A1:D100,,4),INDEX(A1:D1 00,,4),0)+CELL("Row",A1:D1
00)),ROW(A1:D100)))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"GRM via OfficeKB.com" <u14934@uwe wrote in message
news:575aa287bf325@uwe...
I have a database of containers, example as follows:

Column A Column B Column C Column D
Port Load Port Unload Cntr Size Container No
ANT MTL 20 AA123456
ANT MTL 20 AA123456
ANT MTL 20 BC109876
HAM LEH 40 DD294567

I have a formula which counts the number of containers
={COUNT(IF(A1:A100="ANT",IF(B1:B100="MTL",IF(C1:C1 00=20,D1:D100))))}
when entered as an array counts the containers I want, but my problem is
that
I want to exclude duplicated containers in column D.

Any suggestions as to how I could achieve this?

--
Message posted via http://www.officekb.com

Thanks RagDye - having problems getting this to work at the moment. Doesn't
seem to like the Countif

Geoff

--
Message posted via http://www.officekb.com

Try

=SUM(--(FREQUENCY(IF(($A$1:$A$98=E1)*($B$1:$B$98=F1)*($C$ 1:$C$98=G1),MATCH($D$1:$D$98,$D$1:$D$98,0)),ROW(IN DIRECT("1:"&ROWS($D$1:$D$98))))0))

entered as array formula

Port Load in E1
Port Unload in D1
Conatainer size in F1

HTH

Declan O'R

Very simple, use PIVOT TABLE with Count on Container No.
--
Tomek Polak, http://vba.blog.onet.pl

topola wrote:
Very simple, use PIVOT TABLE with Count on Container No.
--
Tomek Polak, http://vba.blog.onet.pl

Thanks Topola - we thought of that, but how would you prevent the counting of
duplicates in a Pivot Table?

--
Message posted via http://www.officekb.com

You're right, I was wrong to some extent. I was thinking about
creating a formula field in a pivot CS1=if(CS0,1,0) and summing it.
This way I can get "1" where a container appears and "0" where it does
not but unfortunately I can not sum it - a sum never exceeds 1.

Sum of CS_1 CS CN
20 20 Suma 40 40 Suma Suma koncowa
PL PU LI AA123456 BC109876 DD294567
ANT MTL 1 1 1 1 0 0 1
HAM LEH 1 0 0 0 1 1 1
Suma koncowa 1 1 1 1 1 1

Of course in a Pivot you can always list the containers (PL, PU, CS, CN
in row, details shown) and see how long this list is.
PL PU CS CN Suma
ANT MTL 20 AA123456 2
BC109876 1
HAM LEH 40 DD294567 1
Suma koncowa 4

cs_range = range in cs column
Match(20,cs_range)=1
Match(40,cs_range)=3
20 lenght is 2=3-1

I am aware this is only a half remedy but sometimes this is enough.
Tomek Polak