specific sum function

[johnaye]

this might be an elementary question but i cant think of the function to do this formula

i'm trying to find a function that will sum up a value if is it greater that 5, and will sum up the total that is greater than 5


i.e. read the following sequence of numbers,

--1,5,6,7,8,
---the function will return 6 (6,7,8 are greater than 5, so 6-5=1, 7-5=2, 8-5=3..so 1+2+3=6

another example
--4,8,14
--- the function will retun 12


thanks so much!

 

[Jonmo1]

Welcome to the board..

Try
=SUMPRODUCT(--(A1:A10>5),A1:A10-5)

 

[johnaye]

i get a #value w/ that formual? :-/

and thanks!

 

[Jonmo1]

Are there any text strings in the range a1:a10 ? Like a header perhaps?

Try
=SUMPRODUCT(--(A2:A10>5),A2:A10-5)