Help with a formula

[David]

Hello,

I'm seeking ideas or solutions to overcome the manual
processes of developing an analysis.

We have a spreadsheet containing the list of production
results by state by company and would like to filter this
list by Top 20%, Middle 60%, & Bottom 20% along with their
total production results based on # of company per that
state.

For example:
AK has 27 companies - 5 @ Top 20%, 17 @ Middle 60%, & 5 @
Bottom 20%

Is there a way to set up a formula to use the table above
and recognize that there are 5 companies belong to Top 20%
and SUM the "Production" of Top 20% (first 5 companies)
automatically? Then, apply this methodology to Middle 60%
and Bottom 20%.

ID Agency Name (Formatted) State Production*
1 Ribelin Lowell & Company Ins AK 362
2 Insurancemart Inc AK 305
3 Acordia Of Alaska Inc AK 243
4 Robert Cederholm & Assoc. Inc. AK 221
5 Randall Moss Insurance Inc AK 183
6 Pippel Insurance Agency Inc AK 180
7 Homer Insurance Center Inc AK 134
8 Kenneth A Murray Insurance Inc AK 132
9 Davies-barry Insurance AK 113
10 Aaa Mountainwest Inc AK 98
11 Walters & Associates Ins Inc AK 93
12 Petersburg Insurance Center AK 77
13 J C Morris Agency Inc AK 55
14 Rural Alaska Ins Agency Inc AK 51
15 Venneberg Insurance, Inc. AK 51
16 Brown Agency AK 50
17 Alaska Pacific Insurance Llc AK 46
18 Hagen Insurance AK 46
19 Denali Alaskan Insurance Llc AK 45
20 Integrity Ins Agency Llc AK 43
21 Shattuck & Grummett, Inc. AK 43
22 Luke & Company Ins Services AK 39
23 S & H Inc AK 38
24 Chi Of Alaska Inc AK 26
25 Alaska Service Agency Inc AK 21
26 Gwaltney & Associates Inc AK 9
27 Insurance Group Of Alaska Llc AK 0
* It was sorted by Production results

Thank you in advance!
David

 

[JE McGimpsey]

One way:

J1&J2 array-entered (CTRL-SHIFT-ENTER or CMD-RETURN), your production
values named "rng":

J1: =SUM(LARGE(rng,ROW(INDIRECT("1:" & INT(COUNT(rng)/5)))))
J2: =SUM(SMALL(rng,ROW(INDIRECT("1:" & INT(COUNT(rng)/5)))))
J3: =SUM(rng,-J1,-J2)