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