Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #7  
Old 02-24-2010, 05:31 AM
BjornS BjornS is offline Need Help With SUMIF Formula In R1C1 Style Windows Vista Need Help With SUMIF Formula In R1C1 Style 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
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Character style stripped while applying paragraph style sams_gates Word 0 08-29-2009 02:03 AM
AutoFormat with style zac Word 0 01-12-2009 01:12 PM
Need to delete style and style text mclan Word 0 08-04-2008 12:05 PM
Style shortcut billzant Word 0 01-06-2007 02:13 AM
Need Help With SUMIF Formula In R1C1 Style Multiple criteria in SUMIF? pumpkin head Excel 1 02-17-2006 09:06 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:21 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft