Add column IF greater than AND ...

[petiteshiraz]

I want to add up the values for an entire column (column D) only if the value entered exceeds $15,000 – but I want to add ONLY the portion of the value in excess of $15,000.

For example, if column D had two entries, one for $17,000 and the other for $8,000 – I would want my result to be $2,000.

How do I write this formula?

 

[edokhotnik]

I want to add up the values for an entire column (column D) only if the value entered exceeds $15,000 – but I want to add ONLY the portion of the value in excess of $15,000.

For example, if column D had two entries, one for $17,000 and the other for $8,000 – I would want my result to be $2,000.

How do I write this formula?

I recommend adding a helper column in E and use the following:

Excel Workbook
	D	E	F
2	17000	2000	2000
3	8000	-7000
Sheet1

Excel 2010

Cell Formulas
Range	Formula
E2	=D2-15000
E3	=D3-15000
F2	=SUMIF(E2:E3,">0",E2:E3)

HTH, Ed

 

[Dryver14]

I know someone will come up with a single formula but the simple way to achieve this is in column E

place the formula =If(D?>15000,D?-15000,"")

? being the starting row number then copy down, this will give you all the individual amounts over 15000, you can then add them at the bottom of the column

 

[petiteshiraz]

Unfortunately, the helper colum won't work because I have to do this many many times for several columns.

I really need a single formula to get it done for the whole column.

 

[Michael M]

Another option

Sub test()
Dim lr As Long, r As Long
lr = Range("D" & Rows.Count).End(xlUp).Row
    a = 0
    For r = lr To 1 Step -1
        If Range("D" & r).Value > 15000 Then a = a + Range("D" & r).Value - 15000
    Next r
MsgBox "The total is " & a
End Sub