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

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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!

Register Yourself Login