Summing the top 80% of the values in a column?

**cherman** · 07-15-2010, 08:02 PM

I have a Excel 2007 table linked to an external data source that has a column that shows a value anywhere from 0 up to around 600. This column is sorted in ascending order and ther will always be filters on other columns in the table. Also, the # of records in this table can change. This is on a sheet called Raw Data.

On another sheet, I ideally need to show the sum of the top 80% of the values in this column, while only looking at the records that have been filtered. It would be great to have this done in 1 formula.

If doing this with 1 formula is not an option, I thought I could count the # of records showing after my filters in 1 cell (for example 285). Then I can calculate 80% of this count in another cell (example 228). Then I can use a variant of the formula below (where D3 is the cell with the calculated 80% count).

=SUMPRODUCT(LARGE(A:A,ROW(INDIRECT("1:" & D3))))

I actually have this working with the calculations on the same sheet as the column and when there is no filter.

Any help would be greatly appreciated.

Thanks!

**DonkeyOte** · 07-16-2010, 03:10 AM

IMO in these scenarios it's often useful to make use of an additional column adjacent to source that utilises the SUBTOTAL function on a row by row basis... this column can greatly simplify subsequent formulae.

Based on your requirements of:

Originally Posted by cherman

I thought I could count the # of records showing after my filters in 1 cell (for example 285). Then I can calculate 80% of this count in another cell (example 228). Then I can use a variant of the formula below (where D3 is the cell with the calculated 80% count).

I would suggest perhaps

'Raw Data'!D2:
=IF(SUBTOTAL(2,C4),C4,"")
copied down for all rows
[edit: assumed for sake of demo. that source fields in Table are A:C with numerics in C]

Your summary formula thus becomes:

=SUM('raw data'!$D$2:INDEX('raw data'!$D:$D,MATCH(SMALL('raw data'!$D:$D,ROUNDDOWN(COUNT('raw data'!$D:$D)*0.8,0)),'raw data'!$D:$D,0)))

Notes:
--it is assumed row 1 on Raw Data contains headers
-- we use MATCH of nth value rather than a SUMIF so as to account for possibility of duplicity of nth value where duplicate(s) are to be excluded given restriction is based on 80% count
-- ranges used (ie Col D) is for demo. purposes only - adjust as nec.

**DonkeyOte** · 07-16-2010, 03:36 AM

edit: the above is incorrect actually as it's summing the bottom 80% - oops, modified for the top 80% (again trying to account for possibility of duplicity):

=SUM('raw data'!D:D)-SUM('raw data'!D1:INDEX('raw data'!D:D,MATCH(SMALL('raw data'!D:D,ROUNDUP(COUNT('raw data'!D:D)*0.2,0)),'raw data'!D:D,0)))

**cherman** · 07-22-2010, 04:26 PM

Thanks very much for your help!

I tried out the formula you gave me and I found a couple of problems.

1) There are 8529 rows of data. Your formula seems to be taking the top 6966 rows into consideration and giving a count of 180835. When I manually calculated what the top 80% of the rows were (by counting the total # of rows and multiplying by 80%), I got 6832. The top 6832 rows give a sum of 180299. There is an obvious discrepancy here.

2) The formula doesn't take filtering into consideration, which is a necessity. There will always be filtered rows.

Do you have any suggestions on how to fix these issues?

Thanks!

**DonkeyOte** · 07-22-2010, 05:04 PM

Perhaps it would be simplest to approach from another angle.

Again - assuming same set up as before in so far as values to sum are listed in C (when visible) and that values are listed in Ascending order then:

'Raw Data'!D1
=SUBTOTAL(2,$C$2:$C$10001)

'Raw Data'!D2
=IF(SUBTOTAL(2,$C$2:$C2)<=$D$1,"",SUBTOTAL(9,$C2))
copied down to say C10001

The result is simply the sum of D (less D1 of course)

**daddylonglegs** · 07-22-2010, 06:21 PM

If you want a single formula then try this, assuming data in A2:A10000

=SUBTOTAL(9,INDEX(A2:A10000,LARGE(IF(SUBTOTAL(2,OFFSET(A2,ROW(A2:A10000)-ROW(A2),0)),ROW(A2:A10000)-ROW(A2)+1),ROUND(SUBTOTAL(2,A2:A10000)*0.8,0))):A10000)

confirmed with CTRL+SHIFT+ENTER

**srglt332** · 08-01-2016, 08:55 AM

Hi, i am new user to excel
Need help on summing top values to match 80% of grand total and Count of rows matching 80% value
Would like to learn creating formula / vba based macro to for quicker solution.

Manual Steps followed by me.
1. Sort in descending order
2. Sum total value and obtain grand total
3. Identify 80% of grand total & manually count no. of rows to match 80% value.

Any one to help.. would love to learn more.
(Sample values given below)

101130
316447
103027
970754
1481220
202576
157433
385807
105081
848441
121587
246354
229157
211966
114263

Result observed manually is

Grand total : 5595245
~80% of value to grand total : 4478180
Count of rows for ~80% value : 7

Can anyone help me to drive formula to obtain this !!!!

**AliGW** · 08-01-2016, 09:06 AM

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.