+ Reply to Thread
Results 1 to 5 of 5

array formula: sumprod with vlookup or sumif to search duplicates item once only

  1. 03-29-2012, 04:17 PM #1
    Mayyalondon
    Mayyalondon is offline
    Registered User
    Join Date
    12-13-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    13

    array formula: sumprod with vlookup or sumif to search duplicates item once only

    Hiya

    I cannot get around this.
    Please see example below: Which formula can calculate for me the 5, 1 and 2?
    It looks simple like this but that would be on different sheets and the report would be huge. I need to count duplicates order references once only to get the number of orders per client (so for Connaught I have 10 lines but I need excel to count 5!)
    Many thanks for your help



    Client Number of orders
    Connaught 5
    Harrods 1
    Dorchester 2


    Client Order reference
    Connaught AAA
    Connaught AAA
    Connaught AAB
    Connaught AAB
    Connaught AAC
    Connaught AAD
    Connaught AAD
    Connaught AAD
    Connaught AAE
    Connaught AAE
    Harrods ZZZ
    Dorchester SDT
    Dorchester STT
    Dorchester STT
    Reply With QuoteRegister To Reply
  2. 03-29-2012, 04:32 PM #2
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: array formula: sumprod with vlookup or sumif to search duplicates item once only

    If you have the Clients in A2:A100 and Order refs in B2:B100 then try this formula to get a count of distinct order refs for a specific Client in D2

    =SUMPRODUCT(($A$2:$A$100=D2)*($A$2:$A$100<>"")/COUNTIFS(A$2:A$100,A$2:A$100&"",B$2:B$100,B$2:B$100&""))
    Audere est facere
    Reply With QuoteRegister To Reply
  3. 03-29-2012, 04:34 PM #3
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,425

    Re: array formula: sumprod with vlookup or sumif to search duplicates item once only

    Something like:

    =SUMPRODUCT(--($A$2:$A$16=$A20),--($B$2:$B$16<>$B$1:$B$15))


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  4. 03-30-2012, 04:43 AM #4
    Mayyalondon
    Mayyalondon is offline
    Registered User
    Join Date
    12-13-2011
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: array formula: sumprod with vlookup or sumif to search duplicates item once only

    Thank you so much, would this be an array formula?
    Reply With QuoteRegister To Reply
  5. 03-30-2012, 07:55 AM #5
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: array formula: sumprod with vlookup or sumif to search duplicates item once only

    Both formulas use arrays but they don't need to be entered with CTRL+SHIFT+ENTER so in that sense they are not "array formulas". My suggestion will work with data in any order, TMS solution requires data to be grouped as per your example, i.e. Clients grouped together and then Order refs to be grouped within each client
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1