+ Reply to Thread
Results 1 to 6 of 6

summary count of unique numbers

  1. #1
    Dave Edge
    Guest

    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

    Can anyone help me please!

  2. #2
    Bob Phillips
    Guest

    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. #3
    Jim May
    Guest

    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. #4
    Jim May
    Guest

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


  5. #5
    Bob Phillips
    Guest

    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
    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" <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. #6
    Jim May
    Guest

    Re: summary count of unique numbers

    Bob,
    Thanks I've added your Update to my archive.
    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
    > 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" <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!
    >> >
    >> >

    >>
    >>

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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