View Single Post
 
Old 02-24-2010, 05:31 AM
BjornS BjornS is offline Windows Vista Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

Hi Todd,
The last SUMIF is missing a lot of info. Here is a correction that works for me:

Range("t9").Select
ActiveCell.FormulaR1C1 = "=SUMIF(R[1]C[-13]:R[36]C[-13],"">0"",R[1]C[-14]:R[36]C[-14])+SUMIF(R[1]C[-13]:R[36]C[-13],"">0"",R[1]C[-6]:R[36]C[-6])+SUMIF(R[1]C[-13]:R[36]C[-13],"">0"",R[1]C[-5]:R[36]C[-5])"

Another (shorter) solution is:

Range("t9").Select
ActiveCell.FormulaR1C1 = "=SUMPRODUCT((R[1]C[-13]:R[36]C[-13]>0)*(R[1]C[-14]:R[36]C[-14]+R[1]C[-6]:R[36]C[-6]+R[1]C[-5]:R[36]C[-5]))"

... which corresponds to the more readable:
=SUMPRODUCT((G10:G45>0)*(F10:F45+N10:N45+O10:O45))

Is there a special reason to why you use the R1C1-style?
This code I find it easier to read:
Range("t9").Select
ActiveCell.Formula = "=SUMPRODUCT((G10:G45>0)*(F10:F45+N10:N45+O10:O45) )"

I hope it works now for you!

Kind regards
Bjorn
Reply With Quote