How to sum the difference

[mart_mrexcel]

I have a value in each cell from A2 - G2 e.gl 12, 11, 13, 15, 21, 26 and 25. What i want to do is to sum the difference of the value in each cell that is greater than 10. In the above example the sum difference should be 38 i.e.
12-10 = 2
11-10 = 1
13-10 = 3
15-10 = 5
21-10 = 11
26-10 = 16
======
38

If the cell value is < 10 then it is just 0. My data may range up to H2 - J2.
Help please. thanks

 

[Aladin Akyurek]

Try...

Either: Just enter..

=SUMPRODUCT(--($A$2:$J$2>10),--ISNUMBER($A$2:$J$2),$A$2:$J$2-10)

Or: Control+shift+enter, not just enter...

=SUM(IF(ISNUMBER($A$2:$J$2),IF($A$2:$J$2>0,$A$2:$J$2-10)))

 

[Mr_Roscoe]

in cell K2 try this:

=SUMIF(B2:J2,">10",B2:J2)-COUNTIF(B2:J2,">10")*10

 

[mart_mrexcel]

Thanks guys..that works great