+ Reply to Thread
Results 1 to 9 of 9

get distinct values from criteria AND sum all of the values of the distinct

  1. #1
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    get distinct values from criteria AND sum all of the values of the distinct

    A B C
    1 #N/A 980200;# 10
    2 #N/A 980000;# 20
    3 #N/A 980000;Z001 30
    4 #N/A 980000;# 15
    5 #N/A 980000;Z001 15
    6 Something 980000;Z001 100
    7 Something 980000;# 200
    8 Something 980010;Z001 20
    9 #N/A 980010;Z001 20
    10 #N/A 980000;# 25
    11 Something 980000;# 80
    12 #N/A 980030;# 40
    13 Something 980030;# 50
    14 Something 980000;# 60

    Given the table above, what I'd like to accomplish is to get the distinct values on column B from that's with criteria '#N/A' from column A. And then, sum all of the distinct values that matches criteria.

    The output in a new sheet would be like the table below.

    A B
    1 980000;# 60
    2 980030;# 40
    3 980200;# 10
    4 980000;Z001 45
    5 980010;Z001 20

    What would be the most fastest and efficient way of doing it.

    Thanks

  2. #2
    Registered User
    Join Date
    02-08-2017
    Location
    Scotland
    MS-Off Ver
    Office 365
    Posts
    21

    Re: get distinct values from criteria AND sum all of the values of the distinct

    fastest way = pivot table

  3. #3
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: get distinct values from criteria AND sum all of the values of the distinct

    Give this a try (but yes a pivot table is probably the fastest):

    Please Login or Register  to view this content.
    Last edited by Arkadi; 02-10-2017 at 12:32 PM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  4. #4
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: get distinct values from criteria AND sum all of the values of the distinct

    Thanks Arkadi!

    Solved and reps up to you!

    Yes, pivot table is one way, but the purpose of this is to use via vba, thus am asking on the vba post.

    Thanks for all the help everyone.

  5. #5
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: get distinct values from criteria AND sum all of the values of the distinct

    Happy to help dluhut. Thanks for the rep and for taking the time to mark the thread as solved

  6. #6
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: get distinct values from criteria AND sum all of the values of the distinct

    Arkadi,

    Quick question.

    Assume if I want to separate/split xxx;yyy into column B and column C, is there a way to do it?

    Please Login or Register  to view this content.

  7. #7
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: get distinct values from criteria AND sum all of the values of the distinct

    Yes there is... but you say into B and C... of the destination sheet? right now my code was putting the xxx;yyy in col A and the sum in col B "myKey" is the xxx;yyy value, myDict(myKey) has the sum for that unique value

  8. #8
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: get distinct values from criteria AND sum all of the values of the distinct

    Hi Arkadi,

    It's just for my curiosity.

    I was able to 'split' them out by using the LEFT and RIGHT function.

    Please Login or Register  to view this content.
    But was wondering if there's an easier way, such as using split or anything.

    Again, just for curiosity.

  9. #9
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: get distinct values from criteria AND sum all of the values of the distinct

    Please Login or Register  to view this content.
    the above code puts xxx;yyy in column A,
    Please Login or Register  to view this content.
    would put only the part before the ";" in Col A, likewise changing the (0) to a (1) would place the part after the first ";" (i realize you have only one... but split divides the string by the specified characters, so if there were 2 ";" you'd have 3 elements)

+ 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. Sum column and count distinct values in another based on multiple criteria
    By mike.greene in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-12-2015, 11:50 AM
  2. [SOLVED] Sum distinct values with two criteria
    By poptcorn in forum Excel General
    Replies: 5
    Last Post: 03-13-2014, 12:09 PM
  3. [SOLVED] sum distinct values given variable criteria
    By jsorbet in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 05-24-2013, 12:54 AM
  4. Count distinct values that correspond to a criteria
    By Eduard in forum Excel Programming / VBA / Macros
    Replies: 23
    Last Post: 04-22-2013, 01:03 PM
  5. Replies: 5
    Last Post: 03-13-2012, 06:05 AM
  6. Formula to COUNTA distinct values with multiple criteria
    By f0urchette in forum Excel General
    Replies: 4
    Last Post: 02-20-2012, 04:54 AM
  7. Count distinct values with criteria
    By greencardioid in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-29-2008, 06:48 PM

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