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:
To create dynamic names range for Salesman:
Similarly, create named ranges for other conditions
We need to follow the below mentioned steps to launch VB editor
tsales = Application.WorksheetFunction.SumIfs([nsales], [nsalesman], mysalesman, [nregion], myregion, [nproduct], myproduct)
[H6] = tsales
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:
stdate = [H6]
EndDate = [H7]
tsales = Application.WorksheetFunction.SumIfs([nsales], [nsalesman], mysalesman, [nregion], myregion, [nproduct], myproduct, [ndate], ">=" & stdate, [ndate], "<=" & EndDate)
[H8] = tsales
This is how we can use SUMIF multiple criteria in VBA to calculate the total sales between 2 dates.
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.
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!
please how can i make this with userform
Great example, please add the calculation of value with different criteria too.
Thanks a lot Mirwais for the appreciation. 🙂