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









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


Sub VBASumifs()mysalesman = [H3]myregion = [H4]myproduct = [H5]

tsales = Application.WorksheetFunction.SumIfs([nsales], [nsalesman], mysalesman, [nregion], myregion, [nproduct], myproduct)

[H6] = tsales

End Sub



  • 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:

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



  • 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.


  1. Thank you for your great example! In case I have to calculate for more than 1 cell, for example: to calculate total sales from 1-Jan-13 to 31-Dec-14 of laptop by each region, how can I do? Do we have better way I/O type the function 4 times for 4 regions?
    I really appreciate your help!

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.