With a list of unique 'load numbers' in column F, in F1 enter
=SUMPRODUCT(--($A$1:$A$8=F1),--(1/COUNTIF($B$1:$B$8,$B$1:$B$8)))
copy down the column
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"brian thompson3001 via OfficeKB.com" <u15682@uwe wrote in message
news:5fb70e390a218@uwe...
Hi

This is a distribution report. with the following 2000+ rows and10 cols.
One
column headed "load number "contains a number that is the same for
1,2,3,4,5,

6,7,8 or 9 rows, unfortunately this not constant. What I am attempting to
get
is the number of different delivery points (col B) for each load number in
col A
I have tried sub totals but can't get to count only different ones?

Col A Col B
GA1234. Ipswich
GA1234. Norwich
GA1234. Ipswich
GA2345. B'ham
GA2345.Stoke
GA2345.Stafford
GA2345.Stoke
GA2345.Stoke

Hopefully end up with the following result
GA1234 = 2.
GA2345 = 3

Any idea's or direction

Regards

Brian

--
bnt

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200605/1