Find top 4 values in column A, sum adjacent value in B, take only highest duplicate

I have two columns, A & B. I want the top 4 values in column A, then I want to sum their adjacent values in column B. Also, columns need to remain unsorted.

If there are duplicates in column A that make up the top 4, take the one with the highest value in column B.

Example:
A B
1 | 22
4 | 2
5 | 6
2 | 11
3 | 10
4 | 80


Top 4 values from column A: 5,4,3,2. The values it should sum up should be 6+80+10+11.

Tried using helper columns to find the max but don't know how to get it to only pick up the highest duplicate.

EDIT: List is dynamic. Ideally, the engineer can cut and paste their values in columns A & B but will be able to get their total from a single cell.

sorry changed post

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

Shift ctrl and enter for this formula, i'll need to check the max bit

Originally Posted by nathansav

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

Shift ctrl and enter for this formula

How does this account for the duplicate entries and ensuring the higher value in column B is used?

I am just looking at that bit

Hi,

Try this array formula**:

=SUM(INDEX(B1:B6,N(IF(1,ROUND(MOD(LARGE(IF(COUNTIFS(A1:A6,A1:A6,B1:B6,">"&B1:B6)=0,A1:A6+ROW(A1:A6)/10^9),{1,2,3,4}),1)*10^9,1)))))

though you don't say what should be the result if there are less than 4 unique values in column A: since you are in effect discarding all but that with the largest value for each group, the following scenario, for example, would error, since there are only three 'largest' values according to your criteria:

1	22
4	2
2	6
2	11
2	10
4	80

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

@mountney1001

I was thinking at a pivot table solution.

Advice:

If you add an excel file, without confidential information, forummembers can test the given code in your file, without building the sheet themselves.