Adding numbers from a list over a specific value

[Guest]

This should be rather simple, but I'm not getting it. I have a list of 15
numbers, six of whom are over 10. For the numbers over ten, I need to add
the difference between those numbers and 10. For instance, it my list had
12, 11, 13, and 10, I would need excel to add 2+1+3 in a manner where I would
not need to add an additional column or row, just entering a formula in a
single cell.

 

[Don Guillett]

one way
=SUMPRODUCT((B2:B22>10)*B2:B22)-(COUNTIF(B2:B22,">"&10)*10)

 

[Bob Phillips]

=SUM(IF(A1:A15>10,A1:A15-10))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Guest]

Try something like this:

With numbers in A1:A25

B1: =SUMPRODUCT((A1:A25>10)*(A1:A25-10))
That formula works with blanks, but not text cells

Otherwise...These formulas work with both blanks AND text cells

B1: =SUMIF(A1:A25,">10",A1:A25)-COUNTIF(A1:A25,">10")*10

or

B1: =SUMPRODUCT(IF(ISNUMBER(A1:A25),(A1:A25>10)*(A1:A25-10)))
Note: For that array formula, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

This custom function will add the diffence between 10 and the cell value for
all the values in the range that are greater than 10

======================================================
Function OverTen(Rng As Range) As Integer

Dim r As Range
Dim intCellVal As Integer
Dim intCellDifference As Integer
Dim intCellCounter As Integer

Set r = Rng

Application.Volatile
For intCellCounter = 1 To Rng.Cells.Count
intCellVal = Rng.Cells(intCellCounter).Value
If intCellVal > 10 Then
intCellDifference = intCellDifference + _
(intCellVal - 10)
End If
Next intCellCounter

OverTen = intCellDifference

Set r = Nothing

End Function
======================================================

Formula Example:

=OVERTEN(A1:A15)