Excel VBA: MAXIFS Function With Multiple Criteria

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, …)


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

Posted in Blog.

Leave a Reply

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