Excel Array Formula

[b3ray]

Hi,

Referring to the uploaded file, I am trying to calculate the sum of the values in Column C (Value 2) based on the Top 10 values in Column B (Value 1).

I know the =SUM(LARGE(B2:B21,ROW(INDIRECT("1:10")))) function will retrieve the Sum of the Top 10 Values in Column B.

However, I am trying to Sum the values in Column C based on the corresponding Top 10 Values in Column B.

Manually, this would require me to sort Column B by largest to smallest and then sum the Top 10 values in Column C, resulting in an answer of 16.7

 

[NARAYANK991]

Hi ,

If you do not mind using a helper column , see this file.

Narayan

 

[Mike H..]

Hi,

Referring to the uploaded file, I am trying to calculate the sum of the values in Column C (Value 2) based on the Top 10 values in Column B (Value 1).

I know the =SUM(LARGE(B2:B21,ROW(INDIRECT("1:10")))) function will retrieve the Sum of the Top 10 Values in Column B.

However, I am trying to Sum the values in Column C based on the corresponding Top 10 Values in Column B.

Manually, this would require me to sort Column B by largest to smallest and then sum the Top 10 values in Column C, resulting in an answer of 16.7

Hi,

I'm not sure which way around you want this.

Sum of C based on Col B
=SUMPRODUCT((B2:B21>=LARGE(B2:B21,10))*(C2:C21))

Sum of B based on col C

=SUMPRODUCT((C2:C21>=LARGE(C2:C21,10))*(B2:B21))

EDIT. For your data you say that the answer is 16.7 but there is a tie for the 10th highest number in Col B (7) so my formula returns 18.6 which is the sum of C based upon the top 10 in B. If this isn't what you want then you need to define the problem in more detail.

 

[b3ray]

Hi Mike,

It is highly unlikely that I will have tied values in my data set.
Your solution is therefore entirely practical.

I appreciate your response.