count

  • Thread starter Thread starter brian thompson3001 via OfficeKB.com
  • Start date Start date
B

brian thompson3001 via OfficeKB.com

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

Try this:

D1 = GA1234
D2 = GA2345

Enter this formula in E1 and copy down:

=SUMPRODUCT((A$1:A$8=D1)/COUNTIF(B$1:B$8,B$1:B$8))

Biff
 
Assuming that A2:B9 contains the data, let D2 and D3 contain GA1234 and
GA234, then try the following...

E2, copied down:

=SUMPRODUCT(--(A$2:A$9=D2),--(B$2:B$9<>""),--(MATCH(A$2:A$9&B$2:B$9,A$2:A
$9&B$2:B$9,0)=ROW(B$2:B$9)-ROW(B$2)+1))

or

=COUNT(1/FREQUENCY(IF(A$2:A$9=D2,IF(B$2:B$9<>"",MATCH(B$2:B$9,B$2:B$9,0))
),ROW(B$2:B$9)-ROW(B$2)+1))

Note that the latter formula needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 
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
 
thanks to you all for the response. I did use domenic's "frequency" in the
end

Regards
Assuming that A2:B9 contains the data, let D2 and D3 contain GA1234 and
GA234, then try the following...

E2, copied down:

=SUMPRODUCT(--(A$2:A$9=D2),--(B$2:B$9<>""),--(MATCH(A$2:A$9&B$2:B$9,A$2:A
$9&B$2:B$9,0)=ROW(B$2:B$9)-ROW(B$2)+1))

or

=COUNT(1/FREQUENCY(IF(A$2:A$9=D2,IF(B$2:B$9<>"",MATCH(B$2:B$9,B$2:B$9,0))
),ROW(B$2:B$9)-ROW(B$2)+1))

Note that the latter formula needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
Hi
Click to expand...
[quoted text clipped - 25 lines]
Brian
Click to expand...
Click to expand...
 
Back
Top