SUMIF Function With Multiple Criteria using VBA in Microsoft Excel

In this article, we will learn how to use SUMIF function in VBA with multiple criteria 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:-

• For all of the conditions; we need to create Name Ranges

To create dynamic names range for Salesman:

• Press CTRL + F3 shortcut key>Click on New & enter the formula as
• =OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$A:$A)-1)

Similarly, create named ranges for other conditions

• Press CTRL + F3 to open Name Manager Window (refer to lists which 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

nDate=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1)

nProduct=OFFSET(Sheet1!$D$1,1,0,COUNTA(Sheet1!$A:$A)-1)

nRegion=OFFSET(Sheet1!$C$1,1,0,COUNTA(Sheet1!$A:$A)-1)

nSales=OFFSET(Sheet1!$E$1,1,0,COUNTA(Sheet1!$A:$A)-1)

nSalesman=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$A:$A)-1)

We need to follow the below mentioned steps to launch VB editor

• Click on Developer tab
• From Code group, select Visual Basic

• Click on Insert, and then Module

• This will create new module.
• Enter the following code in the Module

• Click on Insert ribbon>Shapes>Draw a picture
• Right click on the picture & click on assign macro

• After assigning the macro; click on the Update Sales button & we will get the output in cell H6

Note: - After changing the sales man name, region and product you have to click on update sales.

To find out the Sales that meets the conditions between 2 dates; following is the snapshot of criteria:

We will use the following code:

• Pressing 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 SUMIF multiple criteria in VBA to calculate the total sales between 2 dates.