SUMIF SUBTOTAL OR SUMPRODUCT?

[Guest]

I want to sum a row ignoring the highest (2) and lowest (2) values BUT some
of the cells have a #DIV0!
All I can get using sum and sub total and sumprod is #DIV0!

thanks

 

[Peo Sjoblom]

Why not fix the cells that return DIV error first

=IF(A1=0,"",B1/A1)

otherwise you need something like

=SUM(IF(ISNUMBER(A1:J1),A1:J1),-SMALL(IF(ISNUMBER(A1:J1),A1:J1),{1;2}),-LARG
E(IF(ISNUMBER(A1:J1),A1:J1),{1;2}))

entered with ctrl + shift & enter

 

[Guest]

Hi,

I have the following worksheet and need to have the totals of the columns
equal the total that they are right now even when both column's values
change. Here's what I mean:

DONE REMAINING

000 136
000 37
000 25
000 46
000 46

Above is how the numbers are originally, and here's what I would like them
to do after inputting just one number (Done).

DONE REMAINING

012 124
010 027
018 007
030 016
015 031

Can someone help me with this function please?

Thanks alot!