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

Report
-
 Prasanna -
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 replies

=sum(A1..A6)
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!