VBA with sumif syntax issues

D

dstepan

Board Regular
Joined
Apr 16, 2009
Messages
160
Hello

I did my best to post using everything suggested but this is the best I can do.
Here is a spreadsheet I am trying to work with.
It is basically up to 200 rows and using columns A:H.
I am also attaching my vba code.
The code will add titles, formatting and some formulas.
I am having trouble with the sumif formulas. I have tried multiple ways to make them work (including to record a macro)to no avail. Everytime I try to run the code it gives me a syntax error.
You will see I have 4 sumif cells that I have run amuck.
Here is my code
Thanks for any suggestions.

Code: 
Sub TBSetup()
'
' TBSetup macro
'
'
Rows("1:4").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
    ActiveCell.FormulaR1C1 = "OTC Services, Inc."
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Trial Balance"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "today()-28"
    Range("G5").Select
    ActiveCell.FormulaR1C1 = "TWC"
    Range("H6").Select
    ActiveCell.FormulaR1C1 = "ar"
    Range("H7").Select
    ActiveCell.FormulaR1C1 = "inv"
    Range("H8").Select
    ActiveCell.FormulaR1C1 = "ap"
    Range("H9").Select
    ActiveCell.FormulaR1C1 = "pp"
    Range("G10").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=+R[-4]C+R[-3]C-R[-2]C-R[-1]C"
    Range("A5:G5").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1:A3").Select
    Selection.Font.Bold = True
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("A3").Select
    Selection.NumberFormat = "mmmm, yyyy"
    Set rng = Worksheets("Sheet1").Range("a3:a3")
    Worksheets("Sheet1").Range("A3").Resize(rng.Rows.Count, rng.Columns.Count).Cells.Value = rng.Cells.Value
    Range("G6").Select
        ActiveCell.FormulaR1C1 = "=SUMIF(a:a,{"1110-00","1113-00","1115-00","1118-00"),E:E)
    Range("G7").Select
        ActiveCell.FormulaR1C1 = "=SUMIF(A6:A200,1310-00,E6:E200)+SUMIF(A6:A200,1312-02,E6:E200)+SUMIF(A6:A200,1312-04,E6:E200)+SUMIF(A6:A200,1321-02,E6:E200)"
    Range("G8").Select
        ActiveCell.FormulaR1C1 = "=SUMIF(A6:A200,2010-00,F6:F200)+SUMIF(A6:A200,2011-00,F6:F22)+SUMIF(A6:A200,2013-00,F6:F200)+SUMIF(A6:A200,2014-00,F6:F200)+SUMIF(A6:A200,2075-00,F6:F22)"
    Range("G9").Select
        ActiveCell.FormulaR1C1 = "SUMIF(A6:A200,2010-00,F6:F200)"
    Range("G10").Select
    Selection.Style = "Comma"
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Columns("a:G").Select
    Columns("a:G").EntireColumn.AutoFit
End Sub


[TABLE="width: 1453, align: left"]
<tbody>[TR]
[TD]1001-00
[/TD]
[TD]ck
[/TD]
[TD] $ 16,357.00
[/TD]
[TD] $ (8,078.29)
[/TD]
[TD] $ 8,278.81
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1002-00
[/TD]
[TD]svg
[/TD]
[TD] $ 10,120.00
[/TD]
[TD] $ 3.32
[/TD]
[TD] $ 10,123.81
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1040-02
[/TD]
[TD]ck2
[/TD]
[TD] $ 465.43
[/TD]
[TD] $ (62.65)
[/TD]
[TD] $ 402.78
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1041-02
[/TD]
[TD]pc
[/TD]
[TD] $ 4.03
[/TD]
[TD] $ (0.92)
[/TD]
[TD] $ 3.10
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1110-00
[/TD]
[TD]a/r
[/TD]
[TD] $ 36,329.15
[/TD]
[TD] $ (4,707.11)
[/TD]
[TD] $ 31,622.04
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1112-00
[/TD]
[TD]a/r us
[/TD]
[TD] $ 10,630.59
[/TD]
[TD] $ -
[/TD]
[TD] $ 10,630.59
[/TD]
[TD] $ -
[/TD]
[TD]sumif(a:a,=to 1110-00,1113-00,1115-00,1116-00,1118-00,E:E)
[/TD]
[/TR]
[TR]
[TD]1113-00
[/TD]
[TD]a/r sm
[/TD]
[TD] $ 9,568.34
[/TD]
[TD] $ 2,315.15
[/TD]
[TD] $ 11,883.49
[/TD]
[TD] $ -
[/TD]
[TD]sumif(a:a,=to 1310-00,1312-02,1312-04,1321-02,E:E)
[/TD]
[/TR]
[TR]
[TD]1115-00
[/TD]
[TD]a/r k
[/TD]
[TD] $ 268.26
[/TD]
[TD] $ (11.36)
[/TD]
[TD] $ 256.90
[/TD]
[TD] $ -
[/TD]
[TD]sumif(a:a,=to 2010-00,2011-00,2013-00,2014-00,2075-00,f:f)
[/TD]
[/TR]
[TR]
[TD]1116-00
[/TD]
[TD]a/r e
[/TD]
[TD] $ 6,841.84
[/TD]
[TD] $ 844.67
[/TD]
[TD] $ 7,686.58
[/TD]
[TD]
[/TD]
[TD]sumif(a:a,=to 2510-00,f:f)
[/TD]
[/TR]
[TR]
[TD]1118-00
[/TD]
[TD]ic
[/TD]
[TD] $ 514.26
[/TD]
[TD] $ (76.56)
[/TD]
[TD] $ 437.70
[/TD]
[TD] $ -
[/TD]
[TD] $ 605,249.94
[/TD]
[/TR]
[TR]
[TD]1170-00
[/TD]
[TD]Vendor Prepayments
[/TD]
[TD] $ -
[/TD]
[TD] $ -
[/TD]
[TD] $ 1,988.05
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1199-00
[/TD]
[TD]Receivables - Other
[/TD]
[TD] $ 1,275.19
[/TD]
[TD] $ (132.31)
[/TD]
[TD] $ 1,142.88
[/TD]
[TD] $ -
[/TD]
[TD]**these are the formulas I need in the cells above
[/TD]
[/TR]
[TR]
[TD]1310-00
[/TD]
[TD]stk
[/TD]
[TD] $ 5,436.04
[/TD]
[TD] $ 279.02
[/TD]
[TD] $ 5,715.07
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1312-02
[/TD]
[TD]inv1
[/TD]
[TD] $ 16,581.47
[/TD]
[TD] $ 3,631.26
[/TD]
[TD] $ 20,212.73
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1312-04
[/TD]
[TD]wip
[/TD]
[TD] $ 1,978.95
[/TD]
[TD] $ 227.72
[/TD]
[TD] $ 2,206.67
[/TD]
[TD] $ -
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1321-02
[/TD]
[TD]
[/TD]
[TD] $ 17.17
[/TD]
[TD] $ 503.25
[/TD]
[TD] $ 520.42
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2010-00
[/TD]
[TD]a/p
[/TD]
[TD] $ (21,537.38)
[/TD]
[TD] $ 660.21
[/TD]
[TD]
[/TD]
[TD] $ 20,877.63
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2011-00
[/TD]
[TD]a/p - other
[/TD]
[TD] $ (239,803.00)
[/TD]
[TD] $ 5,395.00
[/TD]
[TD]
[/TD]
[TD] $ 234,408.00
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2013-00
[/TD]
[TD]a/r - pay
[/TD]
[TD] $ (23,178.69)
[/TD]
[TD] $ (49,403.37)
[/TD]
[TD]
[/TD]
[TD] $ 72,582.06
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2014-00
[/TD]
[TD]Accrual
[/TD]
[TD] $ (81,489.22)
[/TD]
[TD] $ (9,247.42)
[/TD]
[TD]
[/TD]
[TD] $ 90,736.64
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2075-00
[/TD]
[TD]CC
[/TD]
[TD] $ (121,446.03)
[/TD]
[TD] $ 9,609.89
[/TD]
[TD]
[/TD]
[TD] $ 111,836.14
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2510-00
[/TD]
[TD]PP
[/TD]
[TD] $ (22,567.66)
[/TD]
[TD] $ 4,153.84
[/TD]
[TD] $
-
[/TD]
[TD] $ 18,413.83
[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
But you still have to close a curly braces, you have a parenthesis

Code: 
[COLOR=#333333]"=SUMIF(a:a,{"1110-00","1113-00","1115-00","1118-00"[/COLOR][SIZE=4][B][COLOR=#ff0000])[/COLOR][/B][/SIZE][COLOR=#333333],E:E)[/COLOR]

Try:
Code: 
"=SUMIF(a:a,{""1110-00"",""1113-00"",""1115-00"",""1118-00""[SIZE=3][B][COLOR=#ff0000]}[/COLOR][/B][/SIZE],E:E)[B][SIZE=3][COLOR=#ff0000]"[/COLOR][/SIZE][/B]
 
Upvote 0
Is there a certain keystroke I need to use for the curly brackets (CTRL-Shift-Enter) or just select the {}brackets?
 
Upvote 0
dstepan said:
Is there a certain keystroke I need to use for the curly brackets (CTRL-Shift-Enter) or just select the {}brackets?
Click to expand...

In this case only the brackets.

But also the numbers must have double quotes

Code: 
[COLOR=#333333]"=SUMIF(a:a,{[/COLOR][COLOR=#ff0000]""[/COLOR][COLOR=#333333]1110-00[/COLOR][COLOR=#ff0000]""[/COLOR][COLOR=#333333],[/COLOR][COLOR=#ff0000]""[/COLOR][COLOR=#333333]1113-00"",[/COLOR][COLOR=#ff0000]""[/COLOR][COLOR=#333333]1115-00[/COLOR][COLOR=#ff0000]""[/COLOR][COLOR=#333333],[/COLOR][COLOR=#ff0000]""[/COLOR][COLOR=#333333]1118-00[/COLOR][COLOR=#ff0000]""[/COLOR][COLOR=#333333][/COLOR][SIZE=3][COLOR=#333333][B][COLOR=#ff0000]}[/COLOR][/B][/COLOR][/SIZE][COLOR=#333333],E:E)[/COLOR][B][SIZE=3][COLOR=#ff0000]"[/COLOR][/SIZE][/B]
 
Upvote 0
Sorry for the bother but I am still getting errors with the sumif formulas. Below is the following:
1. code for sumifs
2. result of formula inserted into cells
3. formulas entered into the cells via code - for some reason the range shows A-semicolon-left parenthesis-right parenthesis-comma but it ends up with a smiley face and I can't send so

I'm not sure what I am doing wrong

Code: 
Range("G6").Select        ActiveCell.FormulaR1C1 = "=SUMIF(A:A,{""1110-00"",""1113-00"",""1115-00"",""1118-00""},e:e)"
    Range("G7").Select
        ActiveCell.FormulaR1C1 = "=SUMIF(A:A,{""1310-00"",""1312-02"",""1312-04"",""1321-02""},e:e)"
    Range("G8").Select
        ActiveCell.FormulaR1C1 = "=SUMIF(A:A,{""2010-00"",""2011-00"",""2013-00"",""2014-00"",""2075-00""},F:F)"
    Range("G9").Select
        ActiveCell.FormulaR1C1 = "SUMIF(A:A,""2010-00"",F:F)"
[\code]

[TABLE="width: 166"]
<tbody>[TR]
[TD]            TWC[/TD]
[/TR]
[TR]
[TD="align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD="align: center"]#NAME?[/TD]
[/TR]
[TR]
[TD][U]SUMIF(A:A,"2010-",F:F)[/U][/TD]
[/TR]
[TR]
[TD="align: center"]#NAME?[/TD]
[/TR]
</tbody>[/TABLE]
    
[code]
[TABLE="width: 466"]
<tbody>[TR]
[TD]              TWC[/TD]
[/TR]
[TR]
[TD]=SUMIF(AA),{"1110-00","1113-00","1115-00","1118-00"},ee))[/TD]
[/TR]
[TR]
[TD]=SUMIF(AA),{"1310-00","1312-02","1312-04","1321-02"},ee))[/TD]
[/TR]
[TR]
[TD]=SUMIF(AA),{"2010-00","2011-00","2013-00","2014-00","2075-00"},FF))[/TD]
[/TR]
[TR]
[TD][U]SUMIF(A:A,"2010-00",F:F)[/U][/TD]
[/TR]
[TR]
[TD]=+G6+G7-G8-G9[/TD]
[/TR]
</tbody>[/TABLE]
[\code]
 
Upvote 0
dstepan said:
Sorry for the bother but I am still getting errors with the sumif formulas. Below is the following:
1. code for sumifs
2. result of formula inserted into cells
3. formulas entered into the cells via code - for some reason the range shows A-semicolon-left parenthesis-right parenthesis-comma but it ends up with a smiley face and I can't send so

I'm not sure what I am doing wrong

Rich (BB code): 
Range("G6").Select        ActiveCell.FormulaR1C1 = "=SUMIF(A:A,{""1110-00"",""1113-00"",""1115-00"",""1118-00""},e:e)"
    Range("G7").Select
        ActiveCell.FormulaR1C1 = "=SUMIF(A:A,{""1310-00"",""1312-02"",""1312-04"",""1321-02""},e:e)"
    Range("G8").Select
        ActiveCell.FormulaR1C1 = "=SUMIF(A:A,{""2010-00"",""2011-00"",""2013-00"",""2014-00"",""2075-00""},F:F)"
    Range("G9").Select
        ActiveCell.FormulaR1C1 = "SUMIF(A:A,""2010-00"",F:F)"


It should be only with the word "formula" not with "FormulaR1C1". Since you are not referring to the cells as RC

Code: 
Range("G6").Select
ActiveCell.[COLOR=#ff0000]Formula [/COLOR]= "=SUMIF(A:A,{""1110-00"",""1113-00"",""1115-00"",""1118-00""},e:e)"
Range("G7").Select
ActiveCell.[COLOR=#ff0000]Formula [/COLOR]= "=SUMIF(A:A,{""1310-00"",""1312-02"",""1312-04"",""1321-02""},e:e)"
Click to expand...
 
Upvote 0
Oh, I just realized that my sumifs is only picking up the first account number value and not adding all the accounts together.

Basically, the totals for the following cells should be:
G6 I should have $51,886.71.
G7 =$28,654.89
G8 = $530,440.47
G9 = $18,413.83
 
Upvote 0
Sorry, I checked the syntax of the formula, but not the result, I assumed you had tried your formula.
It must be done with a matrix formula, I recommend that you limit the range, for example de A3 a A10:

Code: 
Range("G6").FormulaArray = "=SUM(IF(A3:A10={""1110-00"",""1113-00"",""1115-00""},E3:E10))"
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
584
RoryA
RoryA
G
  • Question Question
How to apply multiple conditional formatting when using macro recorder
Replies
0
Views
679
gazmoz17
G
J
  • Question Question
VBA - Select file, copy sheets, run code in all sheets
Replies
0
Views
622
jocker_boy
J
C
  • Question Question
Excel goes to background when macro finishes
Replies
1
Views
661
CSI_William
C
J
  • Solved
Macro Works for Me but Not Others
Replies
3
Views
2K
Fluff
Fluff

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top