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

=SUMIFS(INPUT!$A:$A;INPUT!$B:$B;OUTPUT!$A4;INPUT!$C:$C;OUTPUT!C$2;INPUT!$D:$D;OUTPUT!$B$1) — Daphn123, Oct 25 '18 at 13:36
Is there already data in the OUTPUT sheet before you start the macro? I mean are the dates and ISIN already there? So only the red bordered area has to be filled by the procedure? Or will the procedure generate the whole sheet? — Pᴇʜ, Oct 25 '18 at 14:50
Dates of all days of the year, the ISIN codes are already in the output tab. So only the red field needs to be filled — Daphn123, Oct 25 '18 at 14:56
See my second answer. I added a VBA solution for the example in the images. — Pᴇʜ, Oct 25 '18 at 15:02
@Daphn123, thank you for the excel formula, I was to quick to delete my previous comment. It helped me to understand how you applied your criterias/and ranges. Thank you for the question!, this was a really fun challenge :D!! — Wizhi, Oct 25 '18 at 17:40
The code PEH made for me was actually a code that transforms the formula into Excel and applies it there. I would like a code that only provides the answers in excel, and not the formulas as well. This will make excell very slow since I have to do a lot of these calculations. Is there maybe still anyone who can help me? :) — Daphn123, Oct 26 '18 at 09:09
I tried to provide one approach using Application.WorksheetFunction.SumIfs() as your code in the question hinted that approach. It only gives the final result in the cell, however the calculation process is not a priority here, since VBA inbuilt function needs to iterate through Dates and ISIN's. If you want a really fast VBA process, then you need to create your own "SUMIF" function that use array to speed it up considerably (but the fastest and best would be to use PivotTable) — Wizhi, Oct 26 '18 at 10:01

Pᴇʜ · Answer 1 · 2018-10-25T12:48:42.010

1

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

Will result in this … Please excuse the german screenshot

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

edited Oct 25 '18 at 12:48

answered Oct 25 '18 at 12:43

Pᴇʜ

56,719
10
49
73

I really appreciate your help! But I have instructions to do it in VBA. Could you maybe provide me with a vba code for this data? Then I can change it to my real file myself. – Daphn123 Oct 25 '18 at 12:48
a) VBA is usually slower than Pivot (using built in features can use multi-threading where VBA cannot). b) VBA is a lot of more work that this. And your code is a way too far from being close to a result in VBA. So it is not only 2 lines to get it done. c) *"I have instructions to …"* is not really an argument if you have a better solution. Probably the instructions are the result of not knowing or thinking about PivotTables. You won't re-invent the wheel if there is already a perfect wheel. – Pᴇʜ Oct 25 '18 at 12:53
I understand what you are saying. But I really have to use VBA. Could you please help me with that? I already inserted the arguments I need. I only need the beginning of the code where the variables are defined and a correctly formulated SumIfs code – Daphn123 Oct 25 '18 at 13:03
I'm sorry, but basically you ask us to do (almost) all the work for you. You have nothing than a variable declaration yet. I told you this is not done in 2 lines of code. You would need much more than you think. • Besides that you didn't ask a question ([Why is “Can someone help me?” not an actual question?](https://meta.stackoverflow.com/a/284237/3219613)) so that's off-topic. The best advice I can give you is: Use a PivotTable – Pᴇʜ Oct 25 '18 at 13:23
I inserted the code I made for my origional file into the question, maybe you can see what is wrong there. – Daphn123 Oct 25 '18 at 14:35

Pᴇʜ · Answer 2 · 2018-10-25T15:08:11.487

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

score 0 · Answer 3 · answered Oct 25 '18 at 17:39

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 :)

Since the OP stated *"I have 800 ISIN codes/products over a multiple year period."* and he was concerned about speed, you have to note that each *write* action to a cell takes a lot of time. That means at least 584000 write actions (for a 2 years period). While that answer is correct and would work it would be much slower than writing a formula (one write action) and converting it into values if necessary (one write action). — Pᴇʜ, Oct 26 '18 at 06:13
I agree with you that the amount of iterations + the VBA sumif, will be essentially slower with larger data, your remarks is relevant. There are some benefits I can see, it's relative easy to modify due to set of ranges, you can use same variables later on if you want to add on to these calculations and it's all in VBA [doesn't mean it's always good or the best use of a program :), but might satisfy OP intentions to some degree]. Additional to that I was looking around at SO and saw very few examples how the formula works, so I think it was a great challenge and good opportunity to show :). — Wizhi, Oct 26 '18 at 07:02
Thank you, this works! If I would want to change the cell where the values are being transported to, how would I have to do that? For example now the values for quantity in the input and output sheet both start in row 2. But now I want to have the results in the output sheet in the range starting in B4 — Daphn123, Oct 26 '18 at 12:14
Please mark this as accepted/solved. [Accepting Answers: How does it work?](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) so other people can see that the question is solved. The grey mark/ticker under the voting buttons. Thanks :) — Wizhi, Oct 26 '18 at 12:22
If you only want to adjust the output range you could use something like: `OutputSheet.Cells(cell_date_row, cell_ISIN_column).Offset(1, -1) = Application.WorksheetFunction.Sumifs(Arg1, .....)`, In this case the output numbers will start at cell `B4`. So we add `.Offset(row, column)` to the output. — Wizhi, Oct 26 '18 at 12:36

SUMIFS with multiple sheets/multiple criteria

3 Answers3