Count excluding Duplicates

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:C100=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:D100,,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:C100=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" <[email protected]> wrote in message
news:%[email protected]...
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:D100,,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:C100=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

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

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" <[email protected]> wrote in message
news:%[email protected]...
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" <[email protected]> wrote in message
news:%[email protected]...
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:D100,,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:C100=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(INDIRECT("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

DOR wrote...
>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(INDIRECT("1:"&ROWS($D$1:$D$98))))>0))
>
>entered as array formula
....

You could also do it as

=COUNT(1/FREQUENCY(($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($D$1:$D$98)-ROW(INDEX($D$1:$D$98,1))))
-(SUMPRODUCT(($A$1:$A$98=E1)*($B$1:$B$98=F1)*($C$1:$C$98=G1))
<ROWS($D$1:$D$98))

without array entry and without volatile functions. If it's certain
there'd always be at least two different routes in the table or row 1
contains column labels that don't match the records below it, this
could be simplified to

=COUNT(1/FREQUENCY(($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($D$1:$D$98)-ROW(INDEX($D$1:$D$98,1))))-1

RagDyer's formula would fubar when the same container is used for
different routes including the specified one.

Thank you DOR and Harlan Grove. Formulas look lengthy and complicated to
understand, but we'll try all of them tomorrow morning at work.

Thanks for your input.

Geoff

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

You're welcome, but you should also thank Bob Phillips, on whose
response to a prior question I based this formula. See

http://tinyurl.com/bmlob

I hope it works for you. It seemed to work with all the conditions I
tested.

Declan O'R

DOR wrote...
>You're welcome, but you should also thank Bob Phillips, on whose
>response to a prior question I based this formula. See
>
>http://tinyurl.com/bmlob
....

So who's going to thank the people who preceded Bob's post by several
years showing mostly the same kind of formula? E.g.,

http://groups.google.com/group/micro...e=source&hl=en

(or http://makeashorterlink.com/?N2BB4282C ).

There's VERY little in these newsgroups that's original. Even the
formulas from the late 1990s (that seems to be about as far back as the
Google Groups archive goes) are mostly restatements of formulas
provided in the message board in CompuServe and the like from pre-web
days.

You're right, I was wrong to some extent. I was thinking about
creating a formula field in a pivot CS1=if(CS>0,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