Count excluding Duplicates

  • Thread starter Thread starter GRM via OfficeKB.com
  • Start date Start date
G

GRM via OfficeKB.com

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?
 
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!
=====================================================

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?
 
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 !
----------------------------------------------------------------------------
-------------------

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!
=====================================================

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?
 
topola said:
Very simple, use PIVOT TABLE with Count on Container No.
Click to expand...

Thanks Topola - we thought of that, but how would you prevent the counting of
duplicates in a Pivot Table?
 
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!
=====================================================


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 !
----------------------------------------------------------------------------
-------------------

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!
=====================================================

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?
 
Thanks RagDye - having problems getting this to work at the moment. Doesn't
seem to like the Countif

Geoff
 
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
Click to expand...
....

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
 
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
Click to expand...
....

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/micr...tions/msg/c73c38d5f4c6ef03?dmode=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
 
Back
Top