How to Use SUMIFs between Two Dates using VBA in Microsoft Excel

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

Sub Sumifs2Dates()

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

End Sub

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.