Hello everyone,
I have a bit of a problem expanding the formula below to
show when sales exceed 50. I do not want it to report
when it is less than 50. The formula sumif all sales and
their values. I will like for it to report in the next
row say column H. Sales exceeded by whatever number. Eg.
If sumif total is 60 and want it to report "Sales
exceeded by 10". Even just the number will do. Thanks for
any help and attempt in advance.

c.Offset(1, 3).Formula = "=Sumif(" & rng1.Offset(0,
3).Address & _
",""Sales""," & rng1.Offset(0,
4).Address & ")"

Are you Paul, too?

Didn't this work?

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



chris wrote:

Hello everyone,
I have a bit of a problem expanding the formula below to
show when sales exceed 50. I do not want it to report
when it is less than 50. The formula sumif all sales and
their values. I will like for it to report in the next
row say column H. Sales exceeded by whatever number. Eg.
If sumif total is 60 and want it to report "Sales
exceeded by 10". Even just the number will do. Thanks for
any help and attempt in advance.

c.Offset(1, 3).Formula = "=Sumif(" & rng1.Offset(0,
3).Address & _
",""Sales""," & rng1.Offset(0,
4).Address & ")"

--

Dave Peterson