CODEWITHSUNDEEP
excelsumpercentage
user4100980
user4100980

Reputation: 147

How to sum largest values in column up to certain percentage in Excel

How can I sum the largest values in a column up to a certain percentage or number? I was trying to somehow use SUM() and SUMIF() with LARGE(), but I haven't been able to figure it out. Ideally, I would like to be able to do this without having to sort the column in descending order since this formula will be applied to multiple columns.

Upvotes: 0

Views: 768

Answers (2)

l1b3rty
l1b3rty

Reputation: 3642

Data is in cells A1:A20

Limit number is in cell B1, some possible formulas:

{=SUM(A1:A20*--(A1:A20>B1))}
=SUMIF(A1:A20;">"&B1;A1:A20)
=SUMPRODUCT(A1:A20;--(A1:A20>B1))

Limit % is in cell B2, some possible formulas:

={SUM(A1:A20*--(A1:A20>MAX(A1:A20)*B2))}
=SUMIF(A1:A20;">"&(B2*MAX(A1:A20));A1:A20)
=SUMPRODUCT(A1:A20;--(A1:A20>B2*MAX(A1:A20)))

({...} are array formulas, use Crtl+Shift+Enter)

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152505

To find the top percentage use:

=SUMPRODUCT(($F$1:$F$10)*(PERCENTRANK.INC($F$1:$F$10,$F$1:$F$10)>0.7))

Where $F$1:$F$10 is your data set. And .7 is the lower threshold.

To find the top nth number:

=SUMPRODUCT(LARGE($F$1:$F$10,ROW(1:5)))

Change the 5 to the number of largest numbers to sum. So this will sum the largest 5 numbers.

To sum everything over a certain number:

=SUMIF($F$1:$F$10,">=" & 10)

Where 10 is the lower threshold.

Upvotes: 0

Related Questions