 # add the sum of a group of cells but drop the two highest values

• I need the formula for add the sum of six cells but also drop the highest two cells

• Re: add the sum of a group of cells but drop the two highest values

=SUM(SMALL(A1:A6,{1,2,3,4}))

That will only work for 6 cells.

EDIT: Actually, that's not true. It will work for any number of cells, but it will only SUM the 4 smallest values.

• Re: add the sum of a group of cells but drop the two highest values

There are number of way to do this.

Another option you can use for a larger data set to exclude the two largest values

=SUMIF(A1:A100,"<"&LARGE(A1:A100,2))

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

• Re: add the sum of a group of cells but drop the two highest values

I haven't tested it, but wouldn't you have to array that k value?

=SUMIF(A1:A100,"<"&LARGE(A1:A100,{1,2}))

Just a question....I don't know for sure.

EDIT: DING...it just hit me......less than the second highest value.....no need to call out the highest value at all. Nevermind....carry on ## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!