sum the excess value in a row of cell Solved/Closed

Question

Hello,
i need to add sum of values as shown in the below example

Ex:

Given Values

8
8.5
9
9.5
8.5
9

From the above set of numbers i need to sum the value which is greater than 8. the answer should be 4.5 
(0+0.5+1+1.5+0.5+1 = 4.5) 

Please help me what simple function can be used in excel'07 to make the above calculation

Regards

Joe Cruix · Answer

=sum(A1..A6)

Ivan-hoe · Answer

Hello prasanna, 
Let's say your values are in cells A1 to A6

The easiest way to do it is to write an intermediate formula.
Write in cells B1 =IF(A1>8;A1-8;0) and extend this formula to B6
Then write in any cell =SUM(B1:B6)

If you wish only one formula
=SUMIF(A1:A6;">8")-(COUNTIF(A1:A6;">8")*8)

Ivan

Sum the excess value in a row of cell

2 responses

Subscribe To Our Newsletter!