Sum if formula
 

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 & ")"

Sum if formula
 

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

Sum if formula
 

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
-----Original Message-----
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" wrote in message
...
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 & ")"



.

Sum if formula
 

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


paul wrote:

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 & ")"

--

Dave Peterson