Conditional Sum of the Highest N Numbers in a Range

[Chris88]

I'm trying to sum the highest N numbers in a range, conditionaly. Through searching, I found a couple ideas, but couldn't quiet make them work.

What I'm trying to do:

Column A has a list of names:
Johnson
Murphy
Johnson
Fredericks
Martin
Murphy

Column B has a corresponding number:
1
2
3
6
8
1

And so on...

Can I write a function that will sum the 10 highest numbers associated w/ a specific name, say Murphy?

Thanks very much...

 

[Aladin Akyurek]

I'm trying to sum the highest N numbers in a range, conditionaly. Through searching, I found a couple ideas, but couldn't quiet make them work.

What I'm trying to do:

Column A has a list of names:
Johnson
Murphy
Johnson
Fredericks
Martin
Murphy

Column B has a corresponding number:
1
2
3
6
8
1

And so on...

Can I write a function that will sum the 10 highest numbers associated w/ a specific name, say Murphy?

Thanks very much...

X2: Murphy

Y2:

Control+shift+enter, not just enter...

=SUM(LARGE(IF(NameRange=X2,NumRange),ROW(INDIRECT("1:"&MIN(10,COUNTIF(NameRange,X2))))))

 

[Chris88]

Thanks very much.

When I use it, I'm getting a number that is slightly off, but pretty close. I'm wondering if it is a problem w/ duplicates? If a number in the top 10 is duplicated, will those count as two seperate numbers in the top 10, or one single value (but counted twice)?

Thanks,

 

[Fazza]

Hi, Chris.

As you've started the thread, I suggest it is best for you to specify what you want.

It is always helpful to have a clear specification of the question and some sample data - both the inputs and the results. It nearly always help too to have a screen image. So readers know if you have headers on your data, if the data is sorted or not, have text and or numbers, merged cells, blank rows, or any other specific detail that might be significant.

You asked originally if you could write a function. I'm not sure what you mean. Have you tried to write a user defined function in VBA and it doesn't work? If so, maybe best to post the code you currently have.

HTH, Fazza

 

[Chris88]

Thanks for the reply - I wasn't being very clear, now that I have re-read my post. Sorry about that.

There is a screenshot below that should show what I'm trying to do. Columns A through F is data on NFL players production, and goes down about 500 rows.

I'm trying to get celss J2, J3, J4, J5, and J6 to show the sums of the top N (N being the corresponding number specified in Column I) numbers in Column F, whose position corresponds to the one in Column H (Column C in the data columns).

So cell J2 should show the sum of the top 30 QB numbers from column F.

Using the tip above, I've run into a couple problems: the sums come up exactly 8 off, possibly because numbers that repeat are counted twice? Each should count individually.

Any help anyone could provide would be much appreciated. Thanks so much - Chris.

 

[Aladin Akyurek]

Thanks very much.

When I use it, I'm getting a number that is slightly off, but pretty close. I'm wondering if it is a problem w/ duplicates? If a number in the top 10 is duplicated, will those count as two seperate numbers in the top 10, or one single value (but counted twice)?

Thanks,

5
5
3
2
1

Given above, top 2 will just include both 5's.

 

[Chris88]

Can you spot what I'm doing wrong? I keep getting a number error.

In the screenshot above, I'd be putting the following into cell J2:

=SUM(LARGE(IF(C:C=H2,F:F),ROW(INDIRECT("1:"&MIN(I2,COUNTIF(C:C,H2))))))

 

[Fazza]

Hi, Chris. Per Aladin's post, please refer to the range of data - not the whole column. HTH, Fazza

 

[Chris88]

perfect - thanks everyone!