I need the formula for add the sum of six cells but also drop the highest two cells
add the sum of a group of cells but drop the two highest values



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))

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!