Results 1 to 10 of 10

Thread: Sum values in one column based on unique, filtered values in another

  1. #1

    Sum values in one column based on unique, filtered values in another



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Please help me!

    I'm trying to sum filtered values from column J (course credits) based on unique values from column F (unique course ID).

    The desired result would return a count of 3 unique values (courses) for column F and a subtotal of 10 (credits) for column J.

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    - - - - - 8212 - - - 3
    - - - - - 8212 - - - 3
    - - - - - 9771 - - - 3
    - - - - - 7877 - - - 4
    - - - - - 7877 - - - 4
    - - - - - 7877 - - - 4

    The formula listed below returns the unique count for column F, but I don't know how to add a parameter that would include the sum J based on F.

    {=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET($F$7:$F$50000,ROW($F$7:$F$50000)-ROW(F$7),0,1)),F$7:F$50000),IF(SUBTOTAL(3,OFFSET($F$7:$F$50000,ROW($F$7:$F$50000)-ROW(F$7),0,1)),$F$7:$F$50000))>0,1))}

    Any help would be greatly appreciated!!!

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by m.rabelo View Post
    Please help me!..........Any help would be greatly appreciated!!!
    Example attach file a lot of help in understanding. Here's an idea if that's the result of the second worksheet (if I understand you well)
    See attached file
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Hi Navic,

    Thank you for responding! I tried incorporating the enclosed formula but it kept returning a zero.

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by m.rabelo View Post
    Hi Navic,

    Thank you for responding! I tried incorporating the enclosed formula but it kept returning a zero.
    Did you remember to press Control+Shift+Enter and not just Enter to complete the matrix formulae ?

  5. #5
    Yes, but it still returned a zero. What I am trying to do is sum the amount of credits each course is generating without double or triple counting them. In my sample table, column F contains the unique course reference number and column J contains the amount of credits for the course. Columns A through E contains information such as the course term, college, department, student etc. When I filter according to either college, term or department, I want to be able to see the total amount of credits according to specific parameters.

  6. #6
    Acolyte candybg's Avatar
    Join Date
    Jul 2015
    Posts
    92
    Articles
    0
    Excel Version
    2010 and 2016
    I would think you should be able to use a pivot table to pull any data/totals you need. Slicers would do your "filtering".

    Hope this helps!
    cbug

  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by m.rabelo View Post
    Yes, but it still returned a zero. What I am trying to do is sum the amount of credits each course is generating without double or triple counting them. In my sample table, column F contains the unique course reference number and column J contains the amount of credits for the course. Columns A through E contains information such as the course term, college, department, student etc. When I filter according to either college, term or department, I want to be able to see the total amount of credits according to specific parameters.
    Hello M.rabelo
    After Id looked at your information, I could see that your familiar with matrix formulae, so I apologise for that.
    I've looked at Navics solution, and his illustration appears to be doing what you require. I would check carefully that you have entered everything correctly in the formula. Sometimes a missing or misplaced bracket can cause an error that won't be picked up by the editor.
    If that doesn't solve it, then I suggest you post an example spreadsheet with a small amount of data, including your formula(e) returning zero, and Im sure we can figure it out.

  8. #8

    Sum values in one column based on unique, filtered values in another

    Hello Hercules,

    I'm attaching a sample spreadsheet that better illustrates what I'm trying to do.

    Thank you so much for looking into this!Formula Sample - 07-29-15.xlsx

  9. #9
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    If the situation permits you add a the extra column
    Using SUMIF function, count how many of conditions in pairs

    You pay attention to the formula in cell D27, connected with the cell B27 where is your text (as condition)

    Code:
    =SUMIF($E$6:$K$23;"GR";$K$6:$K$23)
    or
    =SUMIF($E$6:$K$23;MID(B27;SEARCH("(";B27;1)+1;2);$K$6:$K$23)
    I hope it's helpful as an idea
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  10. #10
    Hi Navic,

    This is really helpful! Thank you so much!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •