Sum the excess value in a row of cell

Solved/Closed
prasanna - Sep 9, 2008 at 08:37 AM
 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

2 responses

Ivan-hoe Posts 433 Registration date Saturday February 16, 2008 Status Member Last seen October 17, 2008 110
Sep 12, 2008 at 05:50 AM
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
1
hi ivan

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

regards
prasanna
0
=sum(A1..A6)
-1