Using Sumif Formula in a For-Loop

Question

I am trying to use a Sumif formula in for Loop. I have 45 rows (starting at row 5) and 1 (column B) column. My Code involve two steps:

1. I use the Count function to calculate the number of filled cells in this (45x1) dataset. Let's say first four cells are filled with four account codes. So, the Count function Returns a value of 4.

2. I use the value of 4 as the Input number for my Loop. It means the sumif should be repeated four times for four different accounts Code.

I also tried sumifs, but it does did not work within the four Loop.

    Sub test()

    dim CountSaved1 as double
    dim Value1 as double

    countSaved1 = worksheetfunction.count(worksheets("CFmapping").Range("B5:B50")) 'Counting the number of filled cells for the loop

    For value1 = 5 to CountSaved1

    Worksheets("Cash flow Statement").Range("B4") = WorksheetFunction.SumIf(Worksheets("BS").Range("A:A"), Worksheets("CFmapping").Range("B" & Value1), Worksheets("BS").Range("G:G"))

    Next Value1

    End Sub

I want to return the sum of the four found values.

Answer 1

If I understand your problem correctly you have a sheet with accounts and amounts and you would like to get the sum by account. The first image here represents my two column Account/amount sheet which I've kept named as "sheet1" the second image represents the end result.

This is the vba code I used to come up with the solution. I've kept comments in the code to help you see how I accomplish what I believe you are trying to do.

    Sub test()

    Dim CountSaved1 As Long
    Dim sum As Long
    Dim ws As Worksheet
    sum = 0

'sort by account first.
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A4:B50")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

' number of rows we will be summing per account.
    CountSaved1 = Range("B5").End(xlDown).Row 'WorksheetFunction.Count(Worksheets("Sheet1").Range("B5:B50")) 'Counting the number of filled cells for the loop

    For Value1 = 5 To CountSaved1

'get the current cell and compare to the next cell.
        Debug.Print (Cells(Value1, 1).Value)

        currentAccount = Cells(Value1, 1).Value 'current account cell
        nextAccount = Cells(Value1 + 1, 1).Value 'next account cell
        priorAccount = Cells(Value1 - 1, 1).Value 'prior account cell

        Debug.Print (Cells(Value1 + 1, 1).Value)

'If the current account equals the next account than sum the values
        If (currentAccount = nextAccount) Then
            sum = Cells(Value1 + 1, 2).Value + sum
        ElseIf (currentAccount <> nextAccount) And (currentAccount = priorAccount) Then
            sum = Cells(Value1 - 1, 2).Value + sum

            cellAddress = getBlankCell("Cash flow Statement")
            Worksheets("Cash flow Statement").Range(cellAddress) = currentAccount
            Worksheets("Cash flow Statement").Range(cellAddress).Offset(0, 1) = sum

' reset sum .
            sum = 0
        End If

'range, criteria, sum range
'Worksheets("Cash flow Statement").Range("B4") = WorksheetFunction.SumIf(Worksheets("BS").Range("A:A"), Worksheets("Sheet1").Range("B" & Value1), Worksheets("BS").Range("G:G"))

        Next Value1

    End Sub

    Function getBlankCell(sheet As String) As String

        Dim ws As Worksheet
        Dim foundCell As String
        Set ws = Worksheets(sheet)

        For Each cell In ws.Columns(1).Cells
            If Len(cell) = 0 Then getBlankCell = cell.Address(): Exit For
            Next cell
        End Function

Answer 2

Results will be published at cell B52. Try:

Option Explicit

Sub test1()

    Dim ArrSource As Variant
    'Refer to the Sheet where your data appears
    With ThisWorkbook.Worksheets("Sheet1")
        'ArrSource represent where your data appears. Using .address(), i convert the range in R1C1 form to to be let say accepted by the Consolidate method as source. 
        ArrSource = .Range("B5:C50").Address(True, True, xlR1C1, False)
        'At range B52 i paste the results
        With .Range("B52")
            'Sources illustrate where the data appears, Xlsum used to sum the results which have the same name in LeftColumn
            .Consolidate Sources:=ArrSource, Function:=xlSum, _
                TopRow:=False, LeftColumn:=True, CreateLinks:=False
        End With

    End With

End Sub