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.

 

img1

 

  • We want to find out the Sales figure that meets the following conditions:-

img2

 

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

img3

 

Similarly, create named ranges for other conditions

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

 

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

img7

 

  • Click on Insert, and then Module

img8

 

  • 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

img9

 

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

img10
img11

 

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

img12

 

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

 

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

img14

 

  • 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

img15

 

This is how we can use SUMIF multiple criteria in VBA to calculate the total sales between 2 dates.

Users are saying about us...

  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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube