SUMIFS with multiple sheets/multiple criteria
sum based on a single criteria across multiple sheets
sumif across multiple sheets excel 2010
sum across multiple sheets
sumif multiple criteria
how to sumif between two sheets
sum based on a multiple criteria across multiple sheets excel 2003
excel sumif over multiple tabs
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
How to Use the SUMIF Function Across Multiple Sheets, a closing parenthesis ) and then press Enter. The result, again, is 14,719. SUMIFS with OR criteria (multiple criteria in different columns) Within each set, all conditions must be true (AND logic) A cell is summed if any set of conditions is true (OR logic)
There is no need for VBA here. You can do this with a simple formula.
Copy the following into your red range:
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
Sum values based on multiple conditions, To do this, add a SUMIF formula to each sheet sheet that uses a criteria cell on the summary sheet. Then when you change the criteria, all linked SUMIF formulas will update. The SUMIF function returns the results in an array of summed values for each brand (criteria value) across multiple sheets. Finally, the SUMPRODUCT function sums all the values of this resulting array returned by the SUMIF function for each criteria value across multiple sheets.
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 "
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..)
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.
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 :)
Excel formula: 3D SUMIF for multiple worksheets, This video demonstrates using Microsoft Excel's SUMIFS function to total up a range of cells Duration: 9:19 Posted: Feb 4, 2017 SUMIFS for criteria across multiple sheets. Here is the formula I'm working on. F1=SUMIFS (C:C,A:A,E1,B:B, 'sheet1:sheet2'!A1) A. B.
Microsoft Excel 2013, When the data is spread out in different worksheets in similar ranges of cells, we can add categorize the data by using the SUMIF function across multiple sheets. This can be done by nesting the SUMIF function and the INDIRECT function. 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.
Multiple Multiple Criteria < Thought, How do you sum numbers based on multiple criteria regading data contained in multiple rows within multiple worksheets..? Here, I have used the SUMIFS function (again, see Dealing with Multiple Criteria for more information), which deals SUMIFS in excel is a conditional formula to calculate the sum, as the same suggests it performs the addition operator on a range of cells when they fulfill multiple if condition or multiple criteria provided in the function, this is an inbuilt function in excel and are widely used as conditional statements.
Google Sheets SUMIFS to sum cells with multiple AND / OR criteria, SUMIFS in Google Sheets explained with formula examples. See how to conditionally sum cells with multiple AND as well as OR criteria. SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) The first 3 arguments are mandatory, additional ranges and their associated criteria are optional. sum_range - one or more cells to sum, required. This can be a single cell, a range of cells or a named range.
- 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?
- 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
- See my second answer. I added a VBA solution for the example in the images.
- Great, thank you!
- 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.
- 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.
- 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
- 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?) so that's off-topic. The best advice I can give you is: Use a PivotTable
- I inserted the code I made for my origional file into the question, maybe you can see what is wrong there.
- 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).
- 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 :).
- 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
- Please mark this as accepted/solved. Accepting Answers: How does it work? so other people can see that the question is solved. The grey mark/ticker under the voting buttons. Thanks :)
- 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.