In this article, you will learn how to use SUMIFs function in VBA between 2 dates in Excel, using VBA code.
To get the output, we will use a combination of OFFSET &COUNTA functions to create Name Manager List.
Let us understand with an example:
We have Sales report for Salesman, Region, and Product for years 2012 to 2014
We want to find out the Sales figure that meets the following conditions between 2 dates
To calculate Sales using VBA code, we have used OFFSET function to create Named ranges for each category.
Press CTRL + F3 to open Name Manager Window (the lists are already created)
Using Paste Names feature in Formulas tab, we will get the entire Defined Name range list in cells.
Click on Paste List
We need to follow the below steps to launch VB editor
Click on Developer tab
From Code group, select Visual Basic
Click on Insert, and then Module
This will create a new module.
Enter the following code in the Module
mysalesman = [H3]
myregion = [H4]
myproduct = [H5]
stdate = [H6]
EndDate = [H7]
tsales = Application.WorksheetFunction.SumIfs([nsales], [nsalesman], mysalesman, [nregion], myregion, [nproduct], myproduct, [ndate], “>=” &stdate, [ndate], “<=” &EndDate)
[H8] = tsales
Press ALT + F8 shortcut key for opening Macro window & then select the macro.
Alternatively, you can press F5 to run the code in VBA screen.
After executing the macro, we will get the output in cell H8
This is how we can use SUMIFS function in VBA to calculate the total sales between 2 dates.