+ Reply to Thread
Results 1 to 7 of 7

Summary statistics based on criteria for unique values in column

  1. 09-23-2012, 02:46 AM #1
    nearlylost
    nearlylost is offline
    Registered User
    Join Date
    09-23-2012
    Location
    Perth, Auz
    MS-Off Ver
    Excel 2007
    Posts
    4

    Summary statistics based on criteria for unique values in column

    Hi,

    I am perplexed by what is likely a simple excel problem and would appreciate any help.

    - I wish to calculate the simple statistics of:

    a. the maximum,
    b. the minimum, and
    c. the total count,

    ..of values from the 'DH' column (table below) which correspond to a value of 'TSC' from the 'Code' column; for each unique value in the 'ID' column.



    Subset of data I have:

    ID, DH, Code
    a 1 TSC
    a 2 TAU
    a 3 TSC
    b 1 TMS
    b 28 TMS
    c 1 TSC
    c 2 TSC
    c 3 TSC
    c 4 TLC
    c 5 TLC
    c 30 TCB
    d 1 TLC
    d 2 TLC
    d 3 TSC
    d 4 TLI


    Required result: (unique values from 'ID' column corresponding to value of 'TSC' from within the 'Code' column):

    ID, DH_max, DH_min, Code_count_TSC
    a 3 1 2
    c 3 1 3
    d 3 3 1


    Thanks for any help! (/useful equations..)
    Reply With QuoteRegister To Reply
  2. 09-23-2012, 03:43 AM #2
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Summary statistics based on criteria for unique values in column

    This can be solved with pivot table. See the file.

    It is also possible to only choose TSC.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  3. 09-23-2012, 03:47 AM #3
    Ace_XL
    Ace_XL is offline
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Summary statistics based on criteria for unique values in column

    For MAX use
    =MAX(IF(($C$2:$C$16="TSC")*($A$2:$A$16=D2),$B$2:$B$16))

    For MIN use
    =MIN(IF(($C$2:$C$16="TSC")*($A$2:$A$16=D2),$B$2:$B$16))

    Both above formula are array formula and need to confirmed weith Ctrl+Shift+Enter and not just Enter

    For Count use
    =COUNTIFS($A$2:$A$16,D2,$C$2:$C$16,"TSC")

    See attached
    Attached Files Attached Files
    Life's a spreadsheet, Excel!
    Say thanks, Click *
    Reply With QuoteRegister To Reply
  4. 09-23-2012, 03:56 AM #4
    nearlylost
    nearlylost is offline
    Registered User
    Join Date
    09-23-2012
    Location
    Perth, Auz
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Summary statistics based on criteria for unique values in column

    Cheers Ace!
    Reply With QuoteRegister To Reply
  5. 09-23-2012, 03:57 AM #5
    nearlylost
    nearlylost is offline
    Registered User
    Join Date
    09-23-2012
    Location
    Perth, Auz
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Summary statistics based on criteria for unique values in column

    Cheers Oeldere; Pivots tables; of course...
    Reply With QuoteRegister To Reply
  6. 09-23-2012, 04:08 AM #6
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Summary statistics based on criteria for unique values in column

    Glad I could help. Thanks for the reply.

    If the question is solved, will you mark your question as solved?

    If you have (other) questions, on this item, just ask.
    Reply With QuoteRegister To Reply
  7. 09-23-2012, 04:10 AM #7
    nearlylost
    nearlylost is offline
    Registered User
    Join Date
    09-23-2012
    Location
    Perth, Auz
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Summary statistics based on criteria for unique values in column

    Yes: I just marked it as solved

    Thanks again.
    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)

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