Sum if formula

[paul]

Help!. I have this sumif formula that calculates total
General items. I will like to extend this formula to show
the total of General items when it exceeds 50. I will
like for it to report on Column G. Example General Items
if 60. It should report as excess of 10 Items or
something like that. Is this possible? If yes, I will
appreciate any help.
This is the current formula:

c.Offset(6, 3).Formula = "=Sumif(" & rng1.Offset(0,
3).Address & _
","" General items ""," & rng1.Offset
(0, 4).Address & ")"

 

[Rob van Gelder]

Paul,

Here's an example:

In a new worksheet, Put values from 1 to 10 in cells A1:A10
In another cell use this formula: =SUMIF(A1:A10,"<5",A1:A10)
1+2+3+4 = 10

Other criteria can be "<=5", ">=17", etc...

There's a Conditional Sum Wizard Add-In which can help you out too.


Rob

 

[paul]

Thanks Rob, but this is not what i am looking for. I need
the difference between the sumif of a column from the amt
50. Then a message box saying excess of 10. Also, this is
to modify a macro.
Thanks again

 

[Dave Peterson]

How about a formula that looks like:

=if(sumif()>50,sumif()-50,0)

But an equivalent (and easier to write):
=max(0,sumif()-50)

So your code could look like:

Option Explicit
Sub testme()

Dim c As Range
Dim Rng1 As Range

'test locations
Set c = ActiveSheet.Range("d9")
Set Rng1 = ActiveSheet.Range("e9:e55")

c.Offset(6, 3).Formula = "=max(0,Sumif(" & Rng1.Offset(0, 3).Address & _
","" General items ""," & Rng1.Offset(0, 4).Address & ")-50)"
End Sub