Welcome to Excel Jet Consult. In this tutorial, we will learn how to use the new MAXIFS Function with multiple criteria in VBA. Let’s get started
MAXIFS Funtion returns the maximum value among cells specified by a given criteria or conditions
MAXIFS Syntax =MAXIFS(max_range, criteria_range1, criteria1, …)
Argument
Max_Range – the range that contains the numeric values to evaluate
Criteria_range1 – the range that contains the first criteria to evaluate
Criteria1 – the unique value from criteria_range1
In this example, we want to find and return the highest Total for Account Manager Abiola David and Product Category that equals Beverages.
Note, named ranges is applied to the data set as seen in the caption below
- Execute ALT + F11 to open the VBA
- Select Module from the Insert tab
- In the code window, execute the line of code as seen in the caption below
Range (“O2”) is the cell that will receive the output.
[Total] is the max_range
[Account_Manager] represent the criteria_range1
[O4] represent the criteria 1
[Product_Category] represent the criteria_range2
[P4] represent criteria2
- Close the VBA Editor
- Insert a rectangular shape as seen in the caption below
The next thing we will do is assign the macro to the shape. To do that:
- Right-click on the shape and select Assign Macro
- In the Assign Macro dialogue box, select MAXIFS_Excel_Function
- Click OK
- Click on the button to run the macro
The maximum value based on the given criteria is ₦4,623.49
Finally, we filtered the data set for Abiola David and Beverages. We can clearly see that the maximum value in cell O2 equals the max value in the Status Bar. See the caption below
That is how to use the MAXIFS function in Excel VBA