+ Reply to Thread
Results 1 to 13 of 13

sumif with unique

  1. 06-21-2019, 03:39 AM #1
    rob171171
    rob171171 is offline
    Registered User
    Join Date
    03-22-2011
    Location
    Ireland
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    18

    sumif with unique

    Hello

    I believe the function I need to use is sumif, but Can I do so by have the criteria set to unique. The req here is for column C to display a count of Column a where column B User iD is unique.

    HTML Code: 
    For example in sample above provided I have 2 entries for N31529VK (Qty 37 & 34) so I need these summed to give N31529VK - 71
    I have 2 entries for N42828ZZ ( Qty 29 + 29) so this should be N42828ZZ - 58

    Thanks in advance

    P.S I should add that I dont know every time what the User ID will be and hence using formula is not an option

    HTML Code: 
    Last edited by rob171171; 06-21-2019 at 03:42 AM.
    Reply With QuoteRegister To Reply
  2. 06-21-2019, 03:39 AM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2504
    Posts
    13,620

    Re: sumif with unique

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Reply With QuoteRegister To Reply
  3. 06-24-2019, 02:46 AM #3
    rob171171
    rob171171 is offline
    Registered User
    Join Date
    03-22-2011
    Location
    Ireland
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    18

    Re: sumif with unique

    Workbook attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 06-24-2019, 02:52 AM #4
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: sumif with unique

    Use Pivot Table


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  5. 06-24-2019, 05:50 AM #5
    rob171171
    rob171171 is offline
    Registered User
    Join Date
    03-22-2011
    Location
    Ireland
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    18

    Re: sumif with unique

    Thanks @Sixthsense. This wont be appropriate to use as I need to run this query on a regular basis and just need the formula to resolve.
    Reply With QuoteRegister To Reply
  6. 06-24-2019, 06:01 AM #6
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,730

    Re: sumif with unique

    By using a PT, each time the raw data is updated, then click on the Refresh Button in the Ribbon and your result is updated.

    An alternate means to this is to use Power Query. Every time the raw data is updated, click on the refresh button to update the result. Here is the Mcode

    Please Login or Register  to view this content.
    Data Range
    A
    B
    1
    Attribute
    Value
    2
    Tom
    15
    3
    Joe
    15
    4
    Mary
    25
    5
    Brian
    25
    6
    Jim
    20
    7
    Claire
    10
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  7. 06-24-2019, 08:16 AM #7
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,785

    Re: sumif with unique

    Maybe in E3
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    and D3
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 06-24-2019, 08:29 AM #8
    izzy-analytics
    izzy-analytics is offline
    Registered User
    Join Date
    06-14-2019
    Location
    Charlotte, NC, USA
    MS-Off Ver
    Office 365
    Posts
    20

    Re: sumif with unique

    Another option.... if you KNOW what the unique values are AND you want the table to be able to expandable.

    1) create a table with the source data by selecting the headers and rows (not the title you input)
    2) insert the following formula into D2 and then drag down to D7

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 06-24-2019, 01:29 PM #9
    rob171171
    rob171171 is offline
    Registered User
    Join Date
    03-22-2011
    Location
    Ireland
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    18

    Re: sumif with unique

    Thank you all for your assistance, For me Fluff13's formuala works best.

    @alasidsman - I'm not familiar with Power Query,perhaps I should look into this for future ref.
    Reply With QuoteRegister To Reply
  10. 06-24-2019, 01:31 PM #10
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,785

    Re: sumif with unique

    You're welcome & thanks for the feedback
    Reply With QuoteRegister To Reply
  11. 06-24-2019, 05:44 PM #11
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,730

    Re: sumif with unique

    @rob171171

    Start by looking at the links in my signature. Also, the book "M is for (Data) Monkey" by Ken Puls and Miguel Escobar is a great starting point.
    Reply With QuoteRegister To Reply
  12. 06-25-2019, 07:34 AM #12
    rob171171
    rob171171 is offline
    Registered User
    Join Date
    03-22-2011
    Location
    Ireland
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    18

    Re: sumif with unique

    Ok, thank you Alan
    Reply With QuoteRegister To Reply
  13. 06-25-2019, 11:50 AM #13
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,730

    Re: sumif with unique

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    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)

Similar Threads

  1. [SOLVED] Unique list macro then sumif
    By makinmomb in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-22-2015, 03:02 PM
  2. SUMIF A or B only if unique
    By tahale in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-21-2015, 05:34 AM
  3. unique across three conditions, then sumif
    By icestationzbra in forum Excel General
    Replies: 3
    Last Post: 05-28-2014, 05:57 PM
  4. [SOLVED] Sum unique values according to criteria (sumif unique values)
    By hmpw in forum Excel General
    Replies: 8
    Last Post: 07-14-2012, 06:21 AM
  5. Sumif Unique
    By bushmills in forum Excel General
    Replies: 5
    Last Post: 03-23-2011, 04:16 PM
  6. SUMIf Unique Values
    By aftabn10 in forum Excel General
    Replies: 5
    Last Post: 04-16-2009, 04:09 AM
  7. Sumif only unique items
    By Jay in forum Excel General
    Replies: 0
    Last Post: 08-09-2006, 11:49 AM

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