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.

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>