Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula to count unique items

    I am creating a summary from given data in which i want to count total Invoice Nos. of differenct category in different delivery status. As a Invoice having multiple procudts and having multiple lines in the database but I want to count the invoice no. only once i.e. how many invoices/Stocktransfers are Delivered/Intransit.

    A sample file is attached for reference.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    In B2:
    =COUNT(1/FREQUENCY(IF('Data Sheet'!$B$2:$B$128=B$1,IF('Data Sheet'!$D$2:$D$128=$A2,'Data Sheet'!$A$2:$A$128)),'Data Sheet'!$A$2:$A$128))
    array-entered with Ctrl+Shift+Enter.
    Then fill down and across.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Nov 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Rory

    It's working, but Invoice No in my original data is in text format and for that it is not working.

    Please suggest alternate function.

    Thanks again.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    And that's why you should provide representative samples.

    Try this:

    =COUNT(1/FREQUENCY(IF('Data Sheet'!$B$2:$B$128=B$1,IF('Data Sheet'!$D$2:$D$128=$A2,MATCH('Data Sheet'!$A$2:$A$128,'Data Sheet'!$A$2:$A$128,0))),MATCH('Data Sheet'!$A$2:$A$128,'Data Sheet'!$A$2:$A$128,0)))
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    New Lounger
    Join Date
    Nov 2014
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanx Rory

Posting Permissions

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