VBA with sumif syntax issues

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


1001-00
ck
$ 16,357.00
$ (8,078.29)
$ 8,278.81
$ -

1002-00
svg
$ 10,120.00
$ 3.32
$ 10,123.81
$ -

1040-02
ck2
$ 465.43
$ (62.65)
$ 402.78
$ -

1041-02
pc
$ 4.03
$ (0.92)
$ 3.10
$ -

1110-00
a/r
$ 36,329.15
$ (4,707.11)
$ 31,622.04
$ -

1112-00
a/r us
$ 10,630.59
$ -
$ 10,630.59
$ -
sumif(a:a,=to 1110-00,1113-00,1115-00,1116-00,1118-00,E:E)
1113-00
a/r sm
$ 9,568.34
$ 2,315.15
$ 11,883.49
$ -
sumif(a:a,=to 1310-00,1312-02,1312-04,1321-02,E:E)
1115-00
a/r k
$ 268.26
$ (11.36)
$ 256.90
$ -
sumif(a:a,=to 2010-00,2011-00,2013-00,2014-00,2075-00,f:f)
1116-00
a/r e
$ 6,841.84
$ 844.67
$ 7,686.58

sumif(a:a,=to 2510-00,f:f)
1118-00
ic
$ 514.26
$ (76.56)
$ 437.70
$ -
$ 605,249.94
1170-00
Vendor Prepayments
$ -
$ -
$ 1,988.05
$ -

1199-00
Receivables - Other
$ 1,275.19
$ (132.31)
$ 1,142.88
$ -
**these are the formulas I need in the cells above
1310-00
stk
$ 5,436.04
$ 279.02
$ 5,715.07
$ -

1312-02
inv1
$ 16,581.47
$ 3,631.26
$ 20,212.73
$ -

1312-04
wip
$ 1,978.95
$ 227.72
$ 2,206.67
$ -

1321-02

$ 17.17
$ 503.25
$ 520.42


2010-00
a/p
$ (21,537.38)
$ 660.21

$ 20,877.63

2011-00
a/p - other
$ (239,803.00)
$ 5,395.00

$ 234,408.00

2013-00
a/r - pay
$ (23,178.69)
$ (49,403.37)

$ 72,582.06







2014-00
Accrual
$ (81,489.22)
$ (9,247.42)

$ 90,736.64

2075-00
CC
$ (121,446.03)
$ 9,609.89

$ 111,836.14

2510-00
PP
$ (22,567.66)
$ 4,153.84
$
-
$ 18,413.83



<tbody>
</tbody>
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
Is there a certain keystroke I need to use for the curly brackets (CTRL-Shift-Enter) or just select the {}brackets?

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
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)"
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
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

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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