+ Reply to Thread
Results 1 to 17 of 17

Sum of unique criteria

  1. #1
    Forum Contributor
    Join Date
    07-06-2021
    Location
    Idaho
    MS-Off Ver
    2016, 365
    Posts
    217

    Sum of unique criteria

    Hello, using Office 365.
    I need my sums to count numbers of unique identifiers. So if there are two numbers associated with the same ID, it needs to count it once instead of doubling it.
    Attached Files Attached Files

  2. #2
    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
    36,850

    Re: Sum of unique criteria

    One way
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    07-06-2021
    Location
    Idaho
    MS-Off Ver
    2016, 365
    Posts
    217

    Re: Sum of unique criteria

    It's not working. The return is #VALUE!

    I modified to fit my cells.
    =SUM(FILTER(UNIQUE($B$3:$H$200),{0,1}))

  4. #4
    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
    36,850

    Re: Sum of unique criteria

    Well, that's not really surprising given that the sample you posted does not represent your actual file.

    How are we supposed to guess what column you want to total?

  5. #5
    Forum Contributor
    Join Date
    07-06-2021
    Location
    Idaho
    MS-Off Ver
    2016, 365
    Posts
    217

    Re: Sum of unique criteria

    So, should I upload a new file to paint a better picture of what I am working with?

  6. #6
    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
    36,850

    Re: Sum of unique criteria

    No.

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But, next time, ask the question you need answering.

  7. #7
    Forum Contributor
    Join Date
    07-06-2021
    Location
    Idaho
    MS-Off Ver
    2016, 365
    Posts
    217

    Re: Sum of unique criteria

    For some reason, when I enter or even retype this formula it's inserting the apostrophe in front of the =. And I'm trying everything to get rid of it.

    Edit:
    I was able to get rid of the apostrophe, but for some reason the formula isn't working. Instead of giving an output it's as if the function is incomplete. I changed the values to suit my appropriate file.

    Edit 2: I was able to figure out why it's not generating anything. When I removed the apostrophe it appeared to have automatically insert a space before the equal sign. Not sure why it's doing that.
    Last edited by kyber; 06-24-2022 at 09:45 AM.

  8. #8
    Forum Contributor
    Join Date
    07-06-2021
    Location
    Idaho
    MS-Off Ver
    2016, 365
    Posts
    217

    Re: Sum of unique criteria

    I just uploaded the closest thing I could to the actual file. I generated generic identifiers and amounts. And based on your formula I modified to =SUM(INDEX(Table1[EOB Check Total],MATCH(UNIQUE(Table1[EOB/Batch Number]),Table1[EOB/Batch Number],0)))

    As I just did it, I got a step closer and now it's just giving me #N/A
    Attached Files Attached Files
    Last edited by kyber; 06-24-2022 at 10:30 AM.

  9. #9
    Forum Contributor
    Join Date
    03-05-2018
    Location
    Tbilisi, Georgia
    MS-Off Ver
    2013, 2016
    Posts
    244

    Re: Sum of unique criteria

    Please try:
    PHP Code: 
    =SUMPRODUCT((MATCH($B$3:$B$69,$B$3:$B$69,)=(ROW($B$3:$B$69)-ROW($B$3)+1))*$H$3:$H$69
    Btw, sum should be 305115 and not 314695. Checked via additional column.
    Attached Files Attached Files

  10. #10
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,054

    Re: Sum of unique criteria

    Please try

    =SUM(UNIQUE(IF({1,0},Table1[EOB/Batch Number],Table1[EOB Check Total])))
    Attached Files Attached Files

  11. #11
    Valued Forum Contributor
    Join Date
    07-23-2013
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    875

    Re: Sum of unique criteria

    The blank rows are causing an error. Try:

    =SUM(INDEX(Table1[EOB Check Total],MATCH(UNIQUE(FILTER(Table1[EOB/Batch Number],Table1[EOB/Batch Number]<>"")),Table1[EOB/Batch Number],0)))

  12. #12
    Forum Contributor
    Join Date
    07-06-2021
    Location
    Idaho
    MS-Off Ver
    2016, 365
    Posts
    217

    Re: Sum of unique criteria

    You are right, my math was wrong. The formula works, but not completely. I notice that when I expand the range of the columns to include the vacant ones that the output goes back to #N/A.

  13. #13
    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
    36,850

    Re: Sum of unique criteria

    The reason you get #N/A! is because of all the unnecessary blank rows in your Table. Delete them and the formula works. However, you have two other solutions that work regardless of whether or not there are blank rows. Note that none of the solutions give the value that you state you expect.


    Kyber 305115 =SUM(INDEX(Table1[EOB Check Total],MATCH(UNIQUE(Table1[EOB/Batch Number]),Table1[EOB/Batch Number],0)))

    Strogg 305115 =SUMPRODUCT((MATCH($B$3:$B$69,$B$3:$B$69,)=(ROW($B$3:$B$69)-ROW($B$3)+1))*$H$3:$H$69)

    Bo_ry 305115 =SUM(UNIQUE(IF({1,0},Table1[EOB/Batch Number],Table1[EOB Check Total])))

  14. #14
    Forum Contributor
    Join Date
    07-06-2021
    Location
    Idaho
    MS-Off Ver
    2016, 365
    Posts
    217

    Re: Sum of unique criteria

    @nick.williams that works, thank you so much!

  15. #15
    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
    36,850

    Re: Sum of unique criteria

    Actually, only Bo_ry's solution works with blank rows out of those three. Nick's solution also caters for blank rows.

  16. #16
    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
    36,850

    Re: Sum of unique criteria

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  17. #17
    Forum Contributor
    Join Date
    03-05-2018
    Location
    Tbilisi, Georgia
    MS-Off Ver
    2013, 2016
    Posts
    244

    Re: Sum of unique criteria

    This formula works with blanks. In Excel 2016 requires CTRL+SHIFT+ENTER.
    PHP Code: 
    =SUM(IFNA((MATCH($B$3:$B$78;$B$3:$B$78;)=(ROW($B$3:$B$78)-ROW($B$3)+1))*$H$3:$H$78;)) 

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] FREQUENCY of unique value and SUM of unique values based on criteria
    By dcathey5 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-05-2017, 11:02 AM
  2. Replies: 2
    Last Post: 01-03-2017, 08:40 AM
  3. [SOLVED] A lookup function based on two criteria; one unique & one not unique to the lookup table
    By Trevasaurus in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-18-2014, 11:35 AM
  4. [SOLVED] Count unique entries based off unique criteria in another column
    By hambly in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-11-2014, 03:33 PM
  5. [SOLVED] Two Criteria Lookup, with two seporate criteria making a unique identifier.
    By Trevasaurus in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-26-2013, 11:00 AM
  6. [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
  7. Get unique value if certain criteria met
    By arvarr in forum Excel General
    Replies: 11
    Last Post: 11-07-2010, 07:37 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1