1 Cell to Formulate Cumulative Total Excluding Top & Bottom 10%

hey guys, i have described my question inside the attachment. basically, i hope to achieve the same result as the cell highlighted in blue without using helper columns. thanks~

hi Kevin, thanks for the reply. that would mean i have to eyeball which cells to exclude. the formula i want needs to exclude the yellow cells on its own

no worries~ =)
dont even know if this is possible

*bump*

if i do have to use a macro, can i create a Function out of it? again, just using 1 cell to formulate without helper columns

Hello benishiryo,

Give this a try.

=SUMPRODUCT((SUBTOTAL(9,OFFSET(C2,,,ROW(C2:C20)-ROW(C2)+1))>C23)*(C22-SUBTOTAL(9,OFFSET(C2,,,ROW(C2:C20)-ROW(C2)+1))+C2:C20>C23),B2:B20,C2:C20)

Or a little shorter with just 1 OFFSET.

=SUMPRODUCT((MMULT(((C22*{0,1})-(SUBTOTAL(9,OFFSET(C2,,,ROW(C2:C20)-ROW(C2)+1))*{-1,1})+(C2:C20*{0,1})>C23)+0,{1;1})=2)+0,B2:B20,C2:C20)

thanks again Haseeb. you're really awesome. couldnt up you twice in a row. will do it another time
=)