VLookup largest 4 values

[Okki1]

Hey guys,

I have a question regarding the following situation. I have an excel file with over 35.000 rows with a similar situation like this:

In the first part are all the sales for different companies, and I would like to find the sum of the top 4 sales of each individual company. Thus, the formula should be put where the dots are, and should calculate the sumo the largest 4 sales number in the second column. Some items (i.e., 7000 companies) in my dataset have over 50 rows of data, and I am only interested in the largest 4 for one part my thesis.

Example:

Company	Sales		Company	Top 4 sales
1	100		1	...
2	200		2	...
4	300		3	...
2	400		4	...
3	500
1	500
4	500
2	400
4	300
1	200

<tbody>
</tbody>

I have tried several options, but I cannot get it done. Thanks in advance!

 

[AliGW]

What have you tried that doesn't work?

What do you mean by "the largest 4"? Do the company numbers in the third column correspond to the company numbers in the first, or do they denote the top four performing companies? If the latter, how is that determined? Is it based on just their top four sales, or is it based on all of their sales (but you want the sum of just their top four)?

There is a lot here that seems ambiguous to me.

 

[anglais428]

=SUMPRODUCT(LARGE(($A$2:$A$11=$D2)*($B$2:$B$11),{1,2,3,4}))

Where your company range is A2:A11 your Sales range is B2:B11 and your Company 1 is positioned in cell D2.

This gives (when autofilled)

1 800
2 1000
3 500
4 1100

 

[Okki1]

I have tried variations of VLOOKUP and IF, combined with LARGEST.

The numbers in the first and third column just corresponds (but the amount is large, i.e., > 7000). But the numbers in the first column are mixed, third column is arranged. The value in the fourth column should, thus, be the sum of the largest 4 sale numbers for each company.

In the thesis these aren't companies but industry segments. I need the top 4 sales for each firm to determine the concentration of each segment (i.e., top 4 / total sales). You get it?

 

[AlanY]

try

Excel 2012
	A	B	C	D	E	F	G	H
1	Company	Sales		Company/Top	1	2	3	4
2	1	150		1	440	400	400	400
3	2	420		2	490	490	470	440
4	3	240		3	390	350	340	240
5	4	330		4	460	440	330	300
6	1	90
7	2	490
8	3	120
9	4	460
10	1	400
11	2	20
12	3	70
13	4	30
14	1	440
15	2	470
16	3	390
17	4	110
18	1	260
19	2	280
20	3	340
21	4	440
22	1	350
23	2	440
24	3	350
25	4	300
26	1	400
27	2	330
28	3	210
29	4	180
30	1	400
31	2	490
Sheet4

Cell Formulas
Range	Formula
E2	=SUMPRODUCT(LARGE(($A$2:$A$991=$D2)*($B$2:$B$991),E$1))

 

[Okki1]

Both formula give #N/A as a result. Is this because there are #N/A values in the "Company" column that should be deleted first?

 

[W8253]

Sort column B (Sales) from largest to smallest

	A	B	C	D	E	F	G	H
	Company	Sales	Company	Count	1st	2nd	3rd	4th
1	3	500	1	3	500	200	100
2	1	500	2	3	400	400	200
3	4	500	3	1	500
4	2	400	4	3	500	300	300
5	2	400
6	4	300
7	4	300
8	2	200
9	1	200
10	1	100

<colgroup><col width="64" style="width:48pt"> <col width="64" span="4" style="width:48pt"> <col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

D2:
=COUNTIFS($A$2:$A$11,C2)

E2:
=IF(COLUMNS($E2:E2)<=$D2,INDEX($B$2:$B$11,SMALL(IF($A$2:$A$11=$C2,
ROW($A$2:$A$11)-ROW($A$2)+1),COLUMNS($E2:E2))),"") --> to be enterred as array formula (Ctrl+Shift+Enter, if done correctly { } brackets will appear.

Then simply drag D2 down and E2 right and down up until the amount of values you want to display

 

[MisterProzilla]

Hi Okki,

Assuming you've got 'Company' in cell A1 and no spacers in between the columns, try this formula in the first dotted cell under 'Top 4 Sales' (cell D2) . Instead of pressing return to enter it, press Ctrl + Shift + Return to make it an array formula.

=IFERROR(SUM(LARGE(IF($A$2:$A$36=$C2,$B$2:$B$36,""),1),LARGE(IF($A$2:$A$36=$C2,$B$2:$B$36,""),2),LARGE(IF($A$2:$A$36=$C2,$B$2:$B$36,""),3),LARGE(IF($A$2:$A$36=$C2,$B$2:$B$36,""),4)),"")

There's probably a more elegant solution, but this works. It'll only show a value where a company has four or more values to count. If you want it to work so that it counts all available values if less than four available, I'd recommend splitting the formula into its separate IFERROR(LARGE(IF())) components, in columns D E F and G to get your four separate highest values in order, then summing them in column H.

Hope that helps