SUM 8 values in a column if corresponding value is in the top 8 values

[rutty]

I have 2 columns and need to determine the top 8 values in Column A and then sum up the corresponding value in Column B.

Example:

Column A: Column B:

26.5 4.89
22 6.87
30 6.68
22 5.9
27 5.93
26 5.02
18 4.19
30 6.39
10 7.15
19 5.12

So in this case the values that would be dropped from summing up Column B would be 7.15, and 4.19 because those have the lowest value in Column A.

I hope this makes sense. I am guessing going to need some type of sumif statement combined with a large statement mabye?
Any help would be greatly appreciated.

[JohnTopley]

Try

=SUM(IF(A1:A10>=LARGE(A1:A10,8),B1:B10))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[davsth]

I would have given John's answer, but if the 9th largest value is the same as the 8th, you would get an unexpected result

[63falcondude]

Another option for you to consider:

=SUMIF(A:A,">="&LARGE(A:A,8),B:B)

[rutty]

That worked. Thanks! My only concern will be like mentioned above when the 9th value is the same as the 8th. Which will happen in some circumstances. I appreciate the help and the quick responses.

Is there a way to take the highest value in column B if the values are the same in Column A

[63falcondude]

If that were the case, there would be two "8th largest" values.

What would you like to happen if that were the case?

[rutty]

Would like to take the higher of the 2 values in column B

[63falcondude]

That complicates things. I would use helper columns here.

With headers in row 1,
C2 =SUM(IF(A2>A$2:A$11,1/COUNTIF(A$2:A$11,A$2:A$11)))+1 Ctrl Shift Enter
D2 =IF(AND(C2<=8,MAX(IF($A$2:$A$11=A2,$B$2:$B$11))=B2),"Yes","") Ctrl Shift Enter

Then your final formula would be =SUMIF(D:D,"Yes",B:B)

I have attached a sample sheet for you to reference.

[rutty]

but that formula didn't count the correct ones. In you example spreedsheet, I would want to not add row 8 and row 10, so 4.19 and 7.15 would not be added, since in column A those are the lowest 2 values.

[63falcondude]

You had originally stated that you wanted to take the sum of the numbers in column B related to the 8 largest values of column A. If there are only 8 values to be summed (due to whatever parameters), then all of those eight values would be summed.

Are you now saying that you want to sum all of the numbers in column B except those related to the two smallest values of column A? That would only be 6 values in this case.

If that's the case, you can use this in D2:
=IF(AND(C2>2,MAX(IF($A$2:$A$11=A2,$B$2:$B$11))=B2),"Yes","") Ctrl Shift Enter

[rutty]

sorry. I have myself confused now. I do want the sum of the numbers in column B related to the 8 largest values in Column A. That is correct.

But in my example I have 10 values in column A, so only the highest 8 of those corresponding column B values would be added.
The highlighted values would be the ones that would be added, the other 2 would not be added. I have attached the spreadsheet and highlighted them.

The hard part is when the value of 8 and 9 are the same, in the first formula it adds both of them up, but I want it to add only the highest in column B at that point.

[63falcondude]

There are too many unsaid assumptions for me to wrap my head around.

What happens if all of the numbers in column A are the same, what happens if there are more (or less) than 10 numbers, what happens if the numbers in column A are the same and the numbers in column B are the same, etc.

Hopefully someone else will be able to assist you. I'm tapping out.

[rutty]

There will only be 10 numbers in Column A, no more than that and no less than that. There will be 10 every time.

The chances of column B being the same are very very slim, so I am not worried about that situation. It would be around 0.0001% chance of being the same.

Column A should be different most of times in the 8th and 9th spot, but if not that is my only concern.

[rutty]

Thanks for your help with this. I did figure this help, but couldn't of without your help. I ended up using the Rank command and then the yes command and it worked out pretty good. Created 2 "dummy" columns for this.

=RANK(B4,B$4:B$13)+SUMPRODUCT((B$4:B$13=B4)*(C4< C$4:C$13))

then =IF(D4<=8,"yes","")

[davsth]

as long as there are not more than 2 tied for 7th the following should work

=SUMIF(A1:A10,">="&LARGE(A1:A10,7),B1:B10)+MAX(IF(A1:A10=LARGE(A1:A10,8),B1:B10)) entered as an array shft ctrl enter