I am trying to create a supplier delivery performance spreadsheet and I need
to be able to show a count of the total number of unique orders placed for
each supplier. The worksheet is sub-totalled on a count of the number of
receipts made but one order can have one to many receipts.

SuppNo OrderNo ReceiptNo
123 9961 1
456 9978 16
456 9982 33
789 9999 46
789 9999 57

Therefore SuppNo 123 has 1 Order(s) and 1 Receipt(s)
456 2 2
789 1 2

Can anyone help me please!

=SUMPRODUCT(--(A2:A200=456),B2:B200)

for orders, change column B to C for recipts.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Edge" <Dave wrote in message
...
I am trying to create a supplier delivery performance spreadsheet and I
need
to be able to show a count of the total number of unique orders placed for
each supplier. The worksheet is sub-totalled on a count of the number of
receipts made but one order can have one to many receipts.

SuppNo OrderNo ReceiptNo
123 9961 1
456 9978 16
456 9982 33
789 9999 46
789 9999 57

Therefore SuppNo 123 has 1 Order(s) and 1 Receipt(s)
456 2 2
789 1 2

Can anyone help me please!

Bob:
I tried an example of this, without success;
All I get are "0"'s...
Could you revisit your formula and verify
that it is the correct approach.
Thanks,
Jim


"Bob Phillips" wrote in message
...
=SUMPRODUCT(--(A2:A200=456),B2:B200)

for orders, change column B to C for recipts.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Edge" <Dave wrote in message
...
I am trying to create a supplier delivery performance spreadsheet and I
need
to be able to show a count of the total number of unique orders placed
for
each supplier. The worksheet is sub-totalled on a count of the number of
receipts made but one order can have one to many receipts.

SuppNo OrderNo ReceiptNo
123 9961 1
456 9978 16
456 9982 33
789 9999 46
789 9999 57

Therefore SuppNo 123 has 1 Order(s) and 1 Receipt(s)
456 2 2
789 1 2

Can anyone help me please!

Dave:
I've come up with a solution which might get you what you want.
It's in a small workbook where I show the required formulas.
Write me at if you would like to receive/review it, giving me
your e-mail address that I might send it (as an attachment).
Jim



"Dave Edge" wrote:

I am trying to create a supplier delivery performance spreadsheet and I need
to be able to show a count of the total number of unique orders placed for
each supplier. The worksheet is sub-totalled on a count of the number of
receipts made but one order can have one to many receipts.

SuppNo OrderNo ReceiptNo
123 9961 1
456 9978 16
456 9982 33
789 9999 46
789 9999 57

Therefore SuppNo 123 has 1 Order(s) and 1 Receipt(s)
456 2 2
789 1 2

Can anyone help me please!

Jim,

I don't get 0, but you are right, the formula doesn't do what the OP wants.
This should do what was asked

=SUM(--(FREQUENCY(IF(A2:A100=456,MATCH(B2:B100,B2:B100,0) ),ROW(INDIRECT("1:"
&ROWS(B2:B100))))0))

it is an array formula, so commit with ctrl-shift-enter
--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:8Q_cf.10302$0l5.5859@dukeread06...
Bob:
I tried an example of this, without success;
All I get are "0"'s...
Could you revisit your formula and verify
that it is the correct approach.
Thanks,
Jim


"Bob Phillips" wrote in message
...
=SUMPRODUCT(--(A2:A200=456),B2:B200)

for orders, change column B to C for recipts.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Edge" <Dave wrote in message
...
I am trying to create a supplier delivery performance spreadsheet and I
need
to be able to show a count of the total number of unique orders placed
for
each supplier. The worksheet is sub-totalled on a count of the number
of
receipts made but one order can have one to many receipts.

SuppNo OrderNo ReceiptNo
123 9961 1
456 9978 16
456 9982 33
789 9999 46
789 9999 57

Therefore SuppNo 123 has 1 Order(s) and 1 Receipt(s)
456 2 2
789 1 2

Can anyone help me please!

Bob,
Thanks I've added your Update to my archive.
Jim

"Bob Phillips" wrote in message
...
Jim,

I don't get 0, but you are right, the formula doesn't do what the OP
wants.
This should do what was asked

=SUM(--(FREQUENCY(IF(A2:A100=456,MATCH(B2:B100,B2:B100,0) ),ROW(INDIRECT("1:"
&ROWS(B2:B100))))0))

it is an array formula, so commit with ctrl-shift-enter
--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jim May" wrote in message
news:8Q_cf.10302$0l5.5859@dukeread06...
Bob:
I tried an example of this, without success;
All I get are "0"'s...
Could you revisit your formula and verify
that it is the correct approach.
Thanks,
Jim


"Bob Phillips" wrote in message
...
=SUMPRODUCT(--(A2:A200=456),B2:B200)

for orders, change column B to C for recipts.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Edge" <Dave wrote in message
...
I am trying to create a supplier delivery performance spreadsheet and
I
need
to be able to show a count of the total number of unique orders placed
for
each supplier. The worksheet is sub-totalled on a count of the number
of
receipts made but one order can have one to many receipts.

SuppNo OrderNo ReceiptNo
123 9961 1
456 9978 16
456 9982 33
789 9999 46
789 9999 57

Therefore SuppNo 123 has 1 Order(s) and 1 Receipt(s)
456 2 2
789 1 2

Can anyone help me please!