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