Microsoft Excel is a powerful software. There are many default Excel Functions that we can use to create formulas. Many educational institutions and business companies use Excel files to store valuable data. Sometimes, we need to find the maximum value based on certain conditions. We can do that by combining the** MAX** and **IF** functions. Moreover, the** MAXIFS** function can also do the job. However, the** AGGREGATE** function can perform a similar task with some additional benefits like ignoring error values. This article will show you 3 ideal examples of using **AGGREGATE **to achieve **MAX IF** behavior in Excel.

**Table of Contents**hide

## How to Use AGGREGATE to Achieve MAX IF Behavior in Excel: 3 Examples

**The AGGREGATE function** can carry out various operations depending on the Function Number that we’ll input in the argument. Then, we can decide on what to ignore by choosing the Option Number. Subsequently, we have to select the Array on which we’ll perform the operations. Here, we’ll show how you can use this function to achieve the **MAX IF **behavior. To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset contains *Region, Product, Net Sales, *& *Invoice Date*. This article will show the process to extract the maximum sales of a product from a particular region.

### 1. Use AGGREGATE Function for MAX IF to Find Last Date in Excel

In our first example, we’ll determine the last invoice date for the product TV. The last date will have the maximum value in terms of numbers. In this regard, we’ll input 14 as the function number. 14 is for the **LARGE **function. If you select function number 4 which is **MAX**, you’ll get an error. Because it can’t calculate based on conditions. Therefore, follow the steps below to perform the task.

**STEPS:**

- First, select cell
**D12**. - Then, type the formula:

`=AGGREGATE(`

- As a result, you’ll see the function numbers along with the function names.
- Double-click the
**LARGE function.**

- Consequently, choose the option number that suits your requirements.
- In this example, we choose
**6**(**Ignore error values**).

- Now, type the array:

`(E5:E10)/(C5:C10=C5)`

- Finally, the formula is:

`=AGGREGATE(14,6,(E5:E10)/(C5:C10=C5),1)`

- Next, press
**Enter**to get the output. - Thus, you’ll get the last invoice date for TV.

**NOTE:**In the array section,

**E5:E10**is the

**Net Sales**range. And

**C5:C10=C5**looks for the product

**TV**.

**1**is for the

**1st**largest number.

**Read More: ****How to Use Excel AGGREGATE Function with Multiple Criteria**

### 2. Determine Maximum Based on Criteria with Excel AGGREGATE Function

Now, we’ll show how you can get the maximum **Net Sales** of **TV**. However, instead of using cell range in the array argument, we’ll input table reference. You can easily create a table by pressing the **Ctrl **and **T** keys together after selecting the desired range. In this example, the condition is **TV**. So, learn the following steps to determine maximum sales based on single criteria with the Excel** AGGREGATE** function. We’ll use **14 **as the function number and** 6 **as the option number for this example also.

**STEPS:**

- Firstly, we create a table (
**Table1**) by selecting the range**B4:E10**. - See the picture below where we have the table.

- After that, input the formula:

`=AGGREGATE(14,6,Table1[`

- Hence, the table headers will pop out.
- Choose
**Net Sales**.

- Afterward, type
**/Table1[**and table headers will appear again. - There, select
**Product**.

- Lastly, the whole formula is:

`=AGGREGATE(14,6,Table1[Net Sales]/(Table1[Product]="TV"),1)`

- Next, press
**Enter**. - Consequently, you’ll get an accurate result.

**NOTE: Table1[Product]=”TV”**looks for

**TV**in the

**Product**range. And

**1**is for the

**1st**maximum value.

**Read More: ****Combining AGGREGATE with IF Function in Excel**

### 3. Insert AGGREGATE to Achieve Maximum Based on Double Criteria

However, we may face real-life problems where we have to find out the maximum value from a range based on multiple conditions. In our last example, we’ll demonstrate how you can do that with the **AGGREGATE **function. The conditions are *West* and *TV* as the *Region* and the *Product* respectively. Hence, follow the process below.

**STEPS:**

- First of all, click cell
**D12**. - Then, type the formula:

`=AGGREGATE(14,6,(B5:B10=B5)`

- Here,
**B5:B10=B5**matches the first condition which is**West**as**Region**.

- Next, type
***(C5:C10=C5)**, and the formula becomes:

`=AGGREGATE(14,6,(B5:B10=B5)*(C5:C10=C5)`

- Subsequently, this part looks for the product
**TV**.

- Finally, put
***D5:D10,1**and the complete formula is:

`=AGGREGATE(14,6,(B5:B10=B5)*(C5:C10=C5)*D5:D10,1)`

- Press
**Enter**. - In this way, it’ll return the maximum value based on double criteria.

**NOTE: D5:D10**is the

*Net Sales*range from which we’ll extract the maximum value.

**1**is for the

**1st**largest value.

**Read More: ****How to Use Conditional AGGREGATE Function in Excel**

**Download Practice Workbook**

Download the following workbook to practice by yourself.

## Conclusion

Henceforth, you will be able to use **AGGREGATE **to achieve **MAX IF **behavior in **Excel **following the above-described examples. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.