The sum of the largest ten numerical values

miu niok 80 Reputation points

I have a Workbook and I want to extract the maximum sum of ten numerical values based on two conditions.

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. Emi Zhang-MSFT 19,131 Reputation points Microsoft Vendor


    I created a sample about your requirement:


    User's image

    Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

    If the response is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Barry Schwarz 1,226 Reputation points

    Some additional details might help us provide a more useful answer.

    Are you looking for a macro or a formula? Do you want both conditions to be true, either, or only one? Have you looked at the IF and LARGE functions?

    For sake of example, assume your data is in A1:A100.

    • In an unused column (e.g., X), in cell X1 enter a formula to copy the value in A1 if the conditions are satisfied and 0 other wise.
    • Copy this formula to X2:X100
    • In any spare 10 cells, enter formulas to extract each of the 10 largest values in X1:X100
    • In the cell to contain the requested sum, enter the formula to sum these 10 cells.
    0 comments No comments