Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts

    SUMIF Function Dilemma (Excel 2003 SP1)

    SUMIF Function Dilemma

    This problem relates back to my recent post under LARGE Function Dilemma.

    See attached sample worksheet, scanned with NAV.

    Column B contains a long list of random numbers (not shown) in no particular order. Column C shows in descending order each specific number that appears in column B. Column D shows how many times (frequency) the corresponding number in C appears in B.

    Now I want to list in descending order in column F every frequency in column D that is 10 or above. And in column G I want to show the corresponding number from column C that goes along with each frequency listed in column F.

    The SUMIF function works up to a point, but I need a way to isolate individual numbers (i.e., cells J12:J13 and J16:J18) when there are multiple entries for the same frequency. See sample worksheet.

    Thanks.

    Robert

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUMIF Function Dilemma (Excel 2003 SP1)

    I would use a little macro for this. See attached version; there is a command button on the worksheet that calls the macro.

    This is the code:

    Sub ExtractNSort()
    Range(Range("C10"), Range("D10").End(xlDown)).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Range("F10:F11"), _
    CopyToRange:=Range("H10:I10")
    Range("H10").Sort Key1:=Range("H10"), Order1:=xlDescending, Header:=xlYes
    End Sub

    (I inserted a row for the field names, and two columns to make place for the criteria)

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Re: SUMIF Function Dilemma (Excel 2003 SP1)

    Thanks for your reply, Hans.

    The macro works great! However, is there any other way to solve the problem without using a macro -- i.e., solving the problem with formulas that will update automatically?

    Robert

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SUMIF Function Dilemma (Excel 2003 SP1)

    Try the attached version, it uses only formulas. I inserted some auxiliary columns; you can hide these if you prefer.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Norwich, Connecticut, USA
    Posts
    150
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Re: SUMIF Function Dilemma (Excel 2003 SP1)

    Thank you very much, Hans. I appreciate your expert help!!!

    Best regards,

    Robert

Posting Permissions

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