SUMIFS with multiple sheets/multiple criteria

Question

I have a SUMIFS function. I want to translate this function into VBA code, but I can not make it work.

Two pictures of my Excel file to show a simplified example.

I have an input tab to provide information on several products which are bought and sold on different dates. The names of the products are shown under ISIN. I want to sum the quantities from the input sheet into the output sheet under certain criteria.

I have the following arguments that needs to be fulfilled:

Dim Arg1 As Range 'the range i want to sum : so quantity

Dim Arg2 As Range 'criteria for range : Dates
Dim Arg3 As Range 'the criteria (range)

Dim Arg4 As Range 'criteria for range : ISIN
Dim Arg5 As Range 'the criteria (range)

Dim Arg6 As Range 'criteria for range : Type
Dim Arg7 As Range 'the criteria (range)

Set Arg1 = ThisWB.Sheets("INPUT").Range("A1:A13")

Set Arg2 = ThisWB.Sheets("INPUT").Range("B1:B13")
Set Arg3 = ThisWB.Sheets("OUTPUT").Range("A4:A8")

Set Arg4 = ThisWB.Sheets("INPUT").Range("C1:C13")

'these are rows (so ISIN codes vertically)
Set Arg5 = ThisWB.Sheets("OUTPUT").Range("B2:E2")

Set Arg6 = ThisWB.Sheets("INPUT").Range("D1:D13")

'This is the criteria that only values under Buy should be summed
Set Arg7 = ThisWB.Sheets("OUTPUT").Range("B2")

I want to sum the quantities, per ISIN code/product in the output file.

The results should be shown in the red outlined box in the output sheet.

This should happen if the dates and Buy task correspond to the ones displayed in the output file

I don't know how I should dim and set the variables correctly. I also don't know how the code will run all the dates and ISIN codes displayed in the output file.

This is the code I have so far for my real Excel sheet. Not for the SIMPLIFIED version I showed before.

Option Explicit

Sub InsertQ()
'Sum Quantities

    'Declare variables
    Dim lastRowData, lastRowInput, I, x, pasteRow As Integer

    Dim shtInput As Worksheet
    Dim shtData As Worksheet

    Dim Arg1 As Range 'the range i want to sum : so quantity

    Dim Arg2 As Range 'criteria for range : Dates
    Dim Arg3 As Range 'the criteria (range)

    Dim Arg4 As Range 'criteria for range : ISIN
    Dim Arg5 As Range 'the criteria (range)

    Dim Arg6 As Range 'criteria for range : Type
    Dim Arg7 As Range 'the criteria (range)

    'Set variables
    Set shtData = Sheets("OUTPUT")
    Set shtInput = Sheets("INPUT")
    lastRowData = shtData.Range("B4").End(xlDown).Row
    lastRowInput = shtInput.Range("A1").End(xlDown).Row
    pasteRow = 5

    Set Arg1 = shtInput.Range("G1:G1048576")

    Set Arg2 = shtInput.Range("J1:J1048576")
    Set Arg3 = shtData.Range("A4:A20")

    Set Arg4 = shtInput.Range("AF1:AF1048576")
    Set Arg5 = shtData.Range("B2:E2")

    Set Arg6 = shtInput.Range("E1:E1048576")
    Set Arg7 = shtData.Range("A2")

    'Deactivate Screen for purpose of performance
    Application.ScreenUpdating = False

    'Code

    For I = 2 To lastRowData
        For x = 2 To lastRowInput
        shtData.Cells(x, I) = _
          Application.WorksheetFunction.SumIfs(Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7)
        Next x

        pasteRow = pasteRow + 1

    Next I

    'Formatting
    lastRowData = shtData.Range("B4").End(xlDown).Row
    shtData.Range("B4:XFD" & lastRowData).NumberFormat = "0.00"
    shtData.Range("E5:E" & lastRowData).NumberFormat = "0.00"

    'Confirm to user
    Application.ScreenUpdating = True

    shtData.Range("A1").Select

End Sub

Answer 1

So by using VBA in build function Application.WorksheetFunction.SumIfs() is possible to achieve what you want. I guess this is not the fastest way to execute SUMIFS, but it's a replicate of the excel function "=SUMIFS()".

A notice, according to my understanding is that the criteria values can only be one single value (not a range) at each criteria placement... therefore we need to loop through each criteria value (Criteria1 and criteria2) as it needs to have only one value in the SUMIFS application). This is done in VBA by "For each .. In ..." loop.

Application.WorksheetFunction.SumIfs(Sum_range, Criteria_range1, Criteria1, Criteria_range2, criteria2, etc..)

Worksheet "Input":

Workhseet "Output":

The Code replicates the right table in the worksheet "Output" in the picture above. We are using some ranges from the other worksheet "Input" and some from this worksheet "Output" where we want the result. (Notice in excel you can use ranges for criteria1, In VBA it's not possible.)

The colour is the =sumifs ranges that we are using across the sheets. Those are replicated in the code.

VBA Code:

Option Explicit
Sub Sumifs()

Dim InputSheet As Worksheet
Dim OutputSheet As Worksheet

Set InputSheet = ActiveWorkbook.Worksheets("Input")
Set OutputSheet = ActiveWorkbook.Worksheets("Output")

Dim Arg1 As Range 'the range i want to sum : so quantity

Dim Arg2 As Range 'criteria for range : Dates
Dim Arg3 As Variant 'the criteria (range)

Dim Arg4 As Range 'criteria for range : ISIN
Dim Arg5 As Variant 'the criteria (range)

Dim Arg6 As Range 'criteria for range : Type
Dim Arg7 As Variant 'the criteria (range)

Set Arg1 = InputSheet.Range("A2:A14") 'Sum_range

Set Arg2 = InputSheet.Range("B2:B14") 'Criteria_range1
Set Arg3 = OutputSheet.Range("A3:A7") 'Criteria1

Set Arg4 = InputSheet.Range("C2:C14") 'Criteria_range2
Set Arg5 = OutputSheet.Range("C2:F2") 'Criteria2 - these are rows (so ISIN codes vertically)

Set Arg6 = InputSheet.Range("D2:D14") 'Criteria_range3
Set Arg7 = OutputSheet.Range("B1")    'Criteria3 - This is the criteria that only values under Buy should be summed


Dim cell_date As Variant
Dim cell_ISIN As Variant
Dim cell_Type As Variant
Dim cell_ISIN_column As Long
Dim cell_date_row As Long

For Each cell_ISIN In Arg5 'Loop through all ISIN codes in range setin Arg 5
    cell_ISIN_column = cell_ISIN.Column 'Get current column for ISIN
    For Each cell_date In Arg3 'Loop through all Dates in range set in Arg3
        cell_date_row = cell_date.Row 'Get current row for date
                'My understanding is that the criteria values can only be only one single value at each criteria... therefore we need to loop through each criteria value (Arg3 and Arg5 needs to have only one value in SUMIFS application).
                OutputSheet.Cells(cell_date_row, cell_ISIN_column) = Application.WorksheetFunction.Sumifs(Arg1, Arg2, cell_date, Arg4, cell_ISIN, Arg6, Arg7)
    Next cell_date 'go to next date
Next cell_ISIN 'go to next ISIN

End Sub

Credit to D_Bester at SO and more inspiration/explantions can be found here in his thread :)

Answer 2

There is no need for VBA here. You can do this with a simple formula.

Copy the following into your red range:

=SUMIFS(INPUT!$A:$A,INPUT!$B:$B,OUTPUT!$A:$A,INPUT!$C:$C,OUTPUT!$2:$2,INPUT!$D:$D,OUTPUT!$B$1)

or write that automatically with VBA:

Option Explicit

Sub WriteFormula()
    With Worksheets("OUTPUT")
        With .Range("C4", .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, .Cells(2, .Columns.Count).End(xlToLeft).Column))
            .Formula = "=SUMIFS(INPUT!$A:$A,INPUT!$B:$B,OUTPUT!$A:$A,INPUT!$C:$C,OUTPUT!$2:$2,INPUT!$D:$D,OUTPUT!$B$1)"
            'if needed as values not forumlas uncomment the following line
            '.Value = .Value
        End With
    End With
End Sub

Answer 3

This would be a good task for a pivot table. Assume the following data …

Will result in this … Please excuse the german screenshot

1. Select all data in your input sheet.
2. Insert a PivotTable (select Insert › PivotTable from the ribbon)
3. Then config the Pivot
   • Add Type to Filter
   • Add ISIN to Columns
   • Add Dates to Rows
   • Add Quantity to Values
4. Select Buy as type in cell B2 and done.