CODEWITHSUNDEEP
excelvbasumifsmultiple-conditions
Sree
Sree

Reputation: 21

Multiple conditions using SumIFs

I am trying to automate sumIfs using multiple criteria for the below sample table.

Column A to F is the data and the output table is displayed in column H through K. The output table sums the values in the data based on condition1 and condition2. I am able to use excel built in formulas to do this task, however the values are 0 when I use a vba code to do this.

enter image description here

Can someone help?

I am new to VBA and the code produced 0 when tried in a For loop for my actual data.

Here is my code that I used for this sample, but this throws an error with the SumRange.

Sub Macro3()

    Dim ws As Worksheet, Lr1 As Long, Lc1 As Long
    Set ws = Worksheets("Sheet1")
    
    Lr1 = ws.Range("A" & Rows.Count).End(xlUp).Row
    Lc1 = ws.Range("A" & Columns.Count).End(xlToLeft).Column
    
    
    For i = 3 To Lr1
   
            ws.Range("I" & i).Formula = "=sumifs(range("B"&i&":"F"&i),B1:F1,I1,B2:F2,I2)"
     
    Next i

End Sub

Also, tried using this:

Sub Macro3()

    Dim ws As Worksheet, Lr1 As Long, Lc1 As Long
    Set ws = Worksheets("Sheet1")
    
    Lr1 = ws.Range("A" & Rows.Count).End(xlUp).Row
    Lc1 = ws.Range("A" & Columns.Count).End(xlToLeft).Column
    
    For i = 3 To Lr1
    
    Range("I" & i).Value = Application.WorksheetFunction.SumIfs(Range(Cells(i, 2), Cells(i, Lc1)), Range("B1:F1"), Range("I1"), Range("B2:F2"), Range("I2"))
     
     Next i
 End Sub

Error 1004: unable to get sumifs property of the worksheet function class

Upvotes: 2

Views: 66

Answers (1)

VBasic2008
VBasic2008

Reputation: 54863

Writing Formulas With VBA: SUMIFS

Option Explicit

Sub WriteSumIfsFormula()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    
    Dim LastRow As Long: LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    Dim rg As Range: Set rg = ws.Range("I3", ws.Cells(LastRow, "K"))
    
    Dim Formula As String ' Pay attention to the dollar signs!
    Formula = "=SUMIFS($B3:$F3,$B$1:$F$1,I$1,$B$2:$F$2,I$2)"
    'Debug.Print Formula ' check until you got it right
    
    rg.Formula = Formula

End Sub

Upvotes: 1

Related Questions