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

img2

We want to find out the Sales figure that meets the following conditions between 2 dates

img3

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)

img4

Using Paste Names feature in Formulas tab, we will get the entire Defined Name range list in cells.

img5

Click on Paste List

img6
img7

We need to follow the below steps to launch VB editor

Click on Developer tab

From Code group, select Visual Basic

img8

Click on Insert, and then Module

img9

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

img10
img11

Press ALT + F8 shortcut key for opening Macro window & then select the macro.

img12

Alternatively, you can press F5 to run the code in VBA screen.

After executing the macro, we will get the output in cell H8

img13

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>