Sum the excess value in a row of cell [Solved/Closed]

- - Latest reply:  Prasanna - Sep 13, 2008 at 10:25 AM
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
See more 

2 replies

0
Thank you
=sum(A1..A6)
Posts
436
Registration date
Saturday February 16, 2008
Last seen
October 17, 2008
0
Thank you
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
hi ivan

this solves my problem
thank you very much for the tips....

regards
prasanna