+ Reply to Thread
Results 1 to 3 of 3

Function to sum the values of a column for all distinct values of another column

  1. 07-31-2015, 02:37 PM #1
    NorthUnit
    NorthUnit is offline
    Registered User
    Join Date
    06-02-2015
    Location
    Oklahoma
    MS-Off Ver
    2010
    Posts
    8

    Function to sum the values of a column for all distinct values of another column

    Hi all,

    I have a report that pulls information about products via SQL query into a QueryTable.
    The columns are: ID_Number, Test, Group, Weight, and Status.
    e.g.

    Please Login or Register  to view this content.
    I want to find the total weight of product that failed. In this case, the weight would be 70. Even though a single product failed three tests, I don't want to triple count the weight.
    How do I sum the values counting only the distinct ID_Numbers?
    Using DSUM(database, field, criteria) as DSUM(QueryTable, Weight, Fail) gives me a weight of 90.

    Any help is appreciated!
    Reply With QuoteRegister To Reply
  2. 07-31-2015, 02:39 PM #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 2402
    Posts
    13,446

    Re: Function to sum the values of a column for all distinct values of another column

    Perhaps try a Pivot Table?
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  3. 07-31-2015, 04:40 PM #3
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Function to sum the values of a column for all distinct values of another column

    Try this...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    1
    ID_Number
    Test
    Group
    Weight
    Status
    Fail
    70
    2
    1
    Test12
    A
    40
    Pass
    3
    2
    Test23
    A
    50
    Pass
    4
    3
    Test18
    A
    30
    Pass
    5
    4
    Test47
    A
    50
    Pass
    6
    4
    Test98
    A
    50
    Pass
    7
    5
    Test34
    A
    10
    Fail
    8
    5
    Test14
    A
    10
    Fail
    9
    5
    Test25
    A
    10
    Fail
    10
    6
    Test41
    A
    50
    Pass
    11
    7
    Test10
    A
    60
    Fail
    12
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    ------


    This array formula** entered in H1:

    =SUM(IF(FREQUENCY(IF(E2:E11=G1,MATCH(A2:A11,A2:A11,0)),ROW(A2:A11)-ROW(A2)+1),D2:D11))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    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] Count Distinct Values in One Column Based on Value in A Different Column
    By GStone in forum Excel General
    Replies: 7
    Last Post: 01-29-2015, 10:14 AM
  2. Summing up values in a column corresponding to unique distinct values in another column
    By murti12 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-24-2013, 08:38 AM
  3. How to count distinct values in a column, categorized by values in another column.
    By zhuowang1980 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-16-2013, 03:19 PM
  4. How to write function to get distinct values from column
    By skumarlingam in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-28-2009, 02:54 PM
  5. Excel 2007 : Chart using distinct values in a column
    By sniles in forum Excel General
    Replies: 1
    Last Post: 05-12-2009, 04:53 AM
  6. distinct values in a column
    By ashishprem in forum Excel General
    Replies: 1
    Last Post: 09-07-2006, 12:41 PM
  7. distinct values in column
    By parthaemail in forum Excel - New Users/Basics
    Replies: 13
    Last Post: 03-13-2006, 10:40 AM
  8. [SOLVED] How Do I find distinct values in a Column
    By Stankov in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 09-08-2005, 10:05 AM

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