Thread: SUMIF Function Dilemma (Excel 2003 SP1)

1. 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. 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()
Action:=xlFilterCopy, _
CriteriaRange:=Range("F10:F11"), _
CopyToRange:=Range("H10:I10")
End Sub

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

3. Re: SUMIF Function Dilemma (Excel 2003 SP1)

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. 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. 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
•