Adding numbers from a list over a specific value
 

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.

Adding numbers from a list over a specific value
 

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

--
Don Guillett
SalesAid Software

"Herd96" wrote in message
...
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.

Adding numbers from a list over a specific value
 

=SUM(IF(A1:A1510,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)

"Herd96" wrote in message
...
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.

Adding numbers from a list over a specific value
 

Try something like this:

With numbers in A1:A25

B1: =SUMPRODUCT((A1:A2510)*(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:A2510)*(A1:A2 5-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


"Herd96" wrote:

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.

Adding numbers from a list over a specific value
 

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)

--
Kevin Backmann


"Herd96" wrote:

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.