# summary count of unique numbers

1. ## summary count of unique numbers

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

2. ## Re: summary count of unique numbers

=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 Edge@discussions.microsoft.com> wrote in message
news:8319E1D4-68FD-4E89-A563-153BC8DF3710@microsoft.com...
> 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!

3. ## Re: summary count of unique numbers

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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:efxjRYq5FHA.2008@TK2MSFTNGP10.phx.gbl...
> =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 Edge@discussions.microsoft.com> wrote in message
> news:8319E1D4-68FD-4E89-A563-153BC8DF3710@microsoft.com...
>> 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!

>
>

4. ## RE: summary count of unique numbers

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 jmay@cox.net if you would like to receive/review it, giving me
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!

5. ## Re: summary count of unique numbers

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" <jmay@cox.net> wrote in message
> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:efxjRYq5FHA.2008@TK2MSFTNGP10.phx.gbl...
> > =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 Edge@discussions.microsoft.com> wrote in message
> > news:8319E1D4-68FD-4E89-A563-153BC8DF3710@microsoft.com...
> >> 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!

> >
> >

>
>

6. ## Re: summary count of unique numbers

Bob,
Jim

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:e%23Xg7\$r5FHA.4076@tk2msftngp13.phx.gbl...
> 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" <jmay@cox.net> wrote in message
>> 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" <bob.phillips@notheretiscali.co.uk> wrote in message
>> news:efxjRYq5FHA.2008@TK2MSFTNGP10.phx.gbl...
>> > =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 Edge@discussions.microsoft.com> wrote in message
>> > news:8319E1D4-68FD-4E89-A563-153BC8DF3710@microsoft.com...
>> >> 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!
>> >
>> >

>>
>>

>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1