Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-21-2010, 02:22 PM
Todd Todd is offline Need Help With SUMIF Formula In R1C1 Style Windows Vista Need Help With SUMIF Formula In R1C1 Style Office 2007
Novice
Need Help With SUMIF Formula In R1C1 Style
 
Join Date: Feb 2010
Posts: 6
Todd is on a distinguished road
Default Need Help With SUMIF Formula In R1C1 Style


The formula is =SUMIF(G10:G45,,">0",F10:F45) I try to add it to a macro using the R1C1 style I get a run time error. Here is what I tried to enter.
Range ("t9") . Select
ActiveCell.FormulaR1C1"=SUMIF(R[1]C[-13]:R[36]C[-13],">0",R[1]C[-14]:R[36]C[-14])
This program is about 15 years old and uses many addition and division formulas like the one above. this is the first time trying to use SUMIF in it. Any help would be great. Thanks
Reply With Quote
  #2  
Old 02-22-2010, 02:16 PM
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,
The following 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])"

Note that the quote character ("), is used as delimiter. If you need it INSIDE your statement, you need to use double quotes (see above).

In addition the "=" was missing directly after R1C1 and the quote sign at the end was also missing.

I hope this solves your issue!

Best regards
Bjorn
Reply With Quote
  #3  
Old 02-22-2010, 06:13 PM
Todd Todd is offline Need Help With SUMIF Formula In R1C1 Style Windows Vista Need Help With SUMIF Formula In R1C1 Style Office 2007
Novice
Need Help With SUMIF Formula In R1C1 Style
 
Join Date: Feb 2010
Posts: 6
Todd is on a distinguished road
Default

BJORN it works for me also. Thank you Very Much for your help.
Reply With Quote
  #4  
Old 02-23-2010, 10:05 AM
Todd Todd is offline Need Help With SUMIF Formula In R1C1 Style Windows Vista Need Help With SUMIF Formula In R1C1 Style Office 2007
Novice
Need Help With SUMIF Formula In R1C1 Style
 
Join Date: Feb 2010
Posts: 6
Todd is on a distinguished road
Default

Bjorn
Could you help me again? I decided I wanted to include the sum of two more colums to the formula but again what I entered did not work. Heres what I tried.
Range("t9") .Select
ActiveCell.FormulaR1C1 = "=SUMIF(R[1]C[-13]:R[36]C[-13],"">0"",R[1]C[-14]:R[36]C[-14])+SUM(R[1]C[-6]:R[36]C[-6])+SUM(R[1]C[-5]:R[36]C[-5])"
Reply With Quote
  #5  
Old 02-23-2010, 12:33 PM
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,
your macro works fine for me if I only remove the space after Range("t9").
Like this I mean: Range("t9").Select

This gives the following formula result, when running the macro:
=SUMIF(G10:G45,">0",F10:F45)+SUM(N10:N45)+SUMMA(O1 0:O45)
which seems to be correct.

/Bjorn
Reply With Quote
  #6  
Old 02-23-2010, 05:44 PM
Todd Todd is offline Need Help With SUMIF Formula In R1C1 Style Windows Vista Need Help With SUMIF Formula In R1C1 Style Office 2007
Novice
Need Help With SUMIF Formula In R1C1 Style
 
Join Date: Feb 2010
Posts: 6
Todd is on a distinguished road
Default

BJORN

In my macro I did have the space between the ) and the .Select but must have had something else out of order because I also was able to make it work after I posted. I again did not think the formula all the way thru because when I saw my result I realized I needed the two new columns to have the same SUMIF >0 critera that you help me with to be included in the result in t9. I entered the formula using the +Sumif and it only would work on the N column. I could not get it to work for the O column.Sorry for the confusion. Here the formula I tried to enter.

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[-5]:R[36]C[-5])"

Can you run the macro to see if you can get it to work?

Thanks again
Todd
Reply With Quote
  #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
  #8  
Old 02-24-2010, 10:14 AM
Todd Todd is offline Need Help With SUMIF Formula In R1C1 Style Windows Vista Need Help With SUMIF Formula In R1C1 Style Office 2007
Novice
Need Help With SUMIF Formula In R1C1 Style
 
Join Date: Feb 2010
Posts: 6
Todd is on a distinguished road
Default

Thanks again Bjorn. The reason for the R1C1 style is the macros in this work book were built using it years ago. I am only modifying it to do more than it was originally intended to do. Is their a way to convert it?
Reply With Quote
  #9  
Old 02-24-2010, 03:20 PM
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

Hello Todd,
I don't think there is such a possibility, and I don't think you should touch all that old code. Good luck and I hope the issue is finally closed now!

Best regards
Bjorn
Reply With Quote
  #10  
Old 02-27-2010, 08:30 PM
Todd Todd is offline Need Help With SUMIF Formula In R1C1 Style Windows Vista Need Help With SUMIF Formula In R1C1 Style Office 2007
Novice
Need Help With SUMIF Formula In R1C1 Style
 
Join Date: Feb 2010
Posts: 6
Todd is on a distinguished road
Default

Thanks again BJORN. This is something I decided to try because I had never done it before. The workbook is set up for keeping records for a year by the month. There are option for printing monthly and quarterly reports. All the formulas that you helped me with work. The next set of formulas that I have to make changes to are the quarterly reports. I will try to figure them out myself using what you have taught me so far but if I have trouble I would like to be able to contact you again for help if that's OK with you.
Reply With Quote
Reply

Thread Tools
Display Modes


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 01:44 AM.


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