Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 & ")" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 & ")" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 & ")" . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Commenting custom formula fields/formula on formula editor | Excel Programming |