If you are looking for special tricks to combine **AGGREGATE** with **IF** functions in Excel, youâ€™ve come to the right place. There are numerous ways to combine** AGGREGATE** with** IF** functions in Excel. This article will discuss the details of these examples. Letâ€™s follow the complete guide to learn all of this.

**Table of Contents**Expand

## Combining AGGREGATE with IF Function in Excel: 4 Examples

The following section will use four effective and tricky examples of combining **AGGREGATE** with **IF** functions in Excel. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the **Microsoft 365** version here, but you can utilize any other version according to your preference.

### 1. Finding Highest and Lowest Profit Based on Sales

Here, we will demonstrate combining **AGGREGATE** and **IF** functions with greater than and less than criteria. For demonstration purposes, we will apply the above functions to the following dataset. Here, we will identify the profit based on the sales no greater or less than $2000.

Letâ€™s walk through the following steps to do the task.

**ðŸ“Œ Steps:**

- First of all, select the cell you want to put the value in (cell
**G5**). - Then write down the following formula in it.

`=AGGREGATE(14,6,IF(C5:C15>2000,D5:D15),1)`

- Next, press
**Enter**. - Consequently, you will get the maximum profit based on sales greater than $2000.

- Then, select the cell
**G6**and write down the following formula in it.

`=AGGREGATE(14,6,IF(C5:C15<2000,D5:D15),1)`

- Next, press
**Enter**. - Consequently, you will get the maximum profit based on sales of less than $2000.

- Next, select the cell you want to put the value in (cell
**G9**). - Then write down the following formula in it.

`=AGGREGATE(15,6,IF(C5:C15>2000,D5:D15),1)`

- Next, press
**Enter**. - Consequently, you will get the minimum profit based on sales greater than $2000.

- Next, select the cell and then write down the following formula in it.

`=AGGREGATE(15,6,IF(C5:C15<2000,D5:D15),1)`

- Next, press
**Enter**. - Consequently, you will get the lowest profit based on sales of less than $2000.

**ðŸ”Ž How Does the Formula Work? **

**AGGREGATE(14,6,IF(C5:C15>2000,D5:D15),1)**

Here, the **IF** function will check whether the range of the cell **C5:C15** value is greater than **2000** or not. If it is true then it will collect value from **D5:D15**. Here we got the larger number as result by running an **AGGREGATE function.** Look closely inside the parentheses of the function.

Here,15 = function number, means the **LARGE** function

6= option, means we will ignore error values

1 = 1st largest value (if you want to get the 2nd largest value within a dataset then write 2, if you want to get the 3rd largest value then write 3, and so on)

**AGGREGATE(14,6,IF(C5:C15<2000,D5:D15),1)**

Here, the **IF** function will check whether the range of the cell **C5:C15** value is less than** 2000** or not. If it is true then it will collect value from **D5:D15**.Â Here we got the smaller number as result by running an **AGGREGATE** function. Look closely inside the parentheses of the function.

Here,15 = function number, means the** SMALL** function

6= option, means we will ignore error values

1 = 1st Smallest value (if you want to get the 2nd smallest value within a dataset then write 2, if you want to get the 3rd smallest value then write 3, and so on)

**AGGREGATE(15,6,IF(C5:C15>2000,D5:D15),1)**

Here, the **IF** function will check whether the range of the cell **C5:C15** value is greater than **2000** or not. If it is true then it will collect value from **D5:D15**. Here we got the larger number as result by running an** AGGREGATE** function. Look closely inside the parentheses of the function.

Here,15 = function number, means the** LARGE** function

6= option, means we will ignore error values

1 = 1st largest value (if you want to get the 2nd largest value within a dataset then write 2, if you want to get the 3rd largest value then write 3, and so on)

**AGGREGATE(15,6,IF(C5:C15<2000,D5:D15),1)**

Here, the** IF** function will check whether the range of the cell **C5:C15** value is less than **2000** or not. If it is true then it will collect value from **D5:D15**.Â Here we got the smaller number as result by running an **AGGREGATE** function. Look closely inside the parentheses of the function.

Here, 15 = function number, means the **LARGE** function

6= option, means we will ignore error values

1 = 1st largest value (if you want to get the 2nd largest value within a dataset then write 2, if you want to get the 3rd largest value then write 3, and so on)

**Read More: **How to Use AGGREGATE to Achieve MAX IF Behavior in Excel

### 2. Finding Highest Marks in a Group

Here, we will demonstrate another example of combining **AGGREGATE** and **IF** functions in Excel. For demonstration purposes, we will apply the **AGGREGATE** and** IF** functions to the following dataset. Here, we will identify the highest marks based on the group no equal to 3 or 5.

Letâ€™s walk through the following steps to do the task.

**ðŸ“Œ Steps**:

- First of all, select the cell you want to put the value in (cell
**F5**). - Then write down the following formula in it.

`=AGGREGATE(14,6,IF(C5:C15=3,B5:B15),1)`

- Next, press
**Enter**. - Consequently, you will get the maximum marks based on the group no equal to 3.

- Then, select the cell
**F6**and write down the following formula in it.

`=AGGREGATE(14,6,IF(C5:C15=5,B5:B15),1)`

- Next, press
**Enter**. - Consequently, you will get the maximum marks based on the group not equal to 5.

**ðŸ”Ž How Does the Formula Work? **

**AGGREGATE(14,6,IF(C5:C15=3,B5:B15),1)**

Here, the **IF** function will check whether the range of the cell** C5:C15** value is equal to 3 or not. If it is true then it will collect value from **B5:B15**. Here we got the highest number as result by running an **AGGREGATE** function. Look closely inside the parentheses of the function.

Here, 14 = function number, means the **LARGE** function

6= option, means we will ignore error values

1 = 1st largest value (if you want to get the 2nd largest value within a dataset then write 2, if you want to get the 3rd largest value then write 3, and so on.

**AGGREGATE(14,6,IF(C5:C15=5,B5:B15),1)**

Here, the** IF** function will check whether the range of the cell **C5:C15** value is equal to 5 or not. If it is true then it will collect value from **B5:B15**. Here we got the larger number as result by running an **AGGREGATE** function. Look closely inside the parentheses of the function.

Here, 14 = function number, which means the **LARGE** function

6 = option, which means we will ignore error values

**Read More: **How to Aggregate Data in Excel

### 3. Finding Lowest Marks in a Group

Here, we will illustrate another example of combining Â **AGGREGATE** and **IF** functions in Excel. Here, from the following dataset, we will identify the lowest marks based on the group no equal to 3 or 5.

Letâ€™s walk through the following steps to do the task.

**ðŸ“Œ Steps:**

- First of all, select the cell you want to put the value in (cell
**F5**). - Then write down the following formula in it.

`Â =AGGREGATE(15,6,IF(C5:C15=3,B5:B15),1)`

- Next, press
**Enter**. - Consequently, you will get the minimum marks based on the group not equal to 3.

- Then, select the cell
**F6**and write down the following formula in it.

`=AGGREGATE(15,6,IF(C5:C15=5,B5:B15),1)`

- Next, press
**Enter**. - Consequently, you will get the minimum marks based on the group not equal to 5.

**ðŸ”Ž How Does the Formula Work? **

**AGGREGATE(15,6,IF(C5:C15=3,B5:B15),1)**

Here, the** IF** function will check whether the range of the cell** C5:C15** value is equal to 3 or not. If it is true then it will collect value from **B5:B15**. Here we got the smaller number as result by running an **AGGREGATE** function. Look closely inside the parentheses of the function.

Here, 15 = function number, means the **SMALL** function

6= option, means we will ignore error values

1 = 1st Smallest value (if you want to get the 2nd smallest value within a dataset then write 2, if you want to get the 3rd smallest value then write 3, and so on)

**AGGREGATE(15,6,IF(C5:C15=5,B5:B15),1)**

Here, the **IF** function will check whether the range of the cell **C5:C15** value is equal to 5 or not. If it is true then it will collect value from **B5:B15**. Here we got the smaller number as result by running an **AGGREGATE** function. Look closely inside the parentheses of the function.

Here, 15 = function number, means the **SMALL** function

6= option, means we will ignore error values

1 = 1st Smallest value (if you want to get the 2nd smallest value within a dataset then write 2, if you want to get the 3rd smallest value then write 3, and so on)

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

### 4. Adding Multiple AGGREGATE-IF Formula

Here, we will illustrate the last example of combining multiple **AGGREGATE-IF** formulas in Excel. Here, from the following dataset, we will identify the lowest marks based on groups no equal to 3 and 5.

Letâ€™s walk through the following steps to do the task.

**ðŸ“Œ Steps:**

- First of all, select the cell you want to put the value in (cell
**F5**). - Then write down the following formula in it.

`=AGGREGATE(15,6,IF(C5:C15=3,B5:B15),1)+AGGREGATE(15,6,IF(C5:C15=5,B5:B15),1) `

- Next, press
**Enter**. - Consequently, you will get the minimum marks based on the group no equal to 3 and 5.

**ðŸ”Ž How Does the Formula Work? **

**Formula: AGGREGATE(15,6,IF(C5:C15=3,B5:B15),1)+AGGREGATE(15,6,IF(C5:C15=5,B5:B15),1) **

- Here, the
**IF(C5:C15=3,B5:B15)**function will check whether the range of the cell**C5:C15**value is equal to 3 or not. If it is true then it will collect value from**B5:B15**. Here we got the smaller number as result by running an**AGGREGATE(15,6,IF(C5:C15=3,B5:B15),1)**Look closely inside the parentheses of the function.

Here,15 = function number, means the **SMALL** function

6= option, which means we will ignore error values

- Next, the
**IF(C5:C15=5,B5:B15)**function will check whether the range of the cell**C5:C15**value is equal to 5 or not. If it is true then it will collect value from**B5:B15**. Here we got the smaller number as a result by running an**AGGREGATE(15,6,IF(C5:C15=5,B5:B15),1)** - In the final stage, we sum up the both
**AGGREGATE**function and get the desired output as 52.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.

## Conclusion

Thatâ€™s the end of todayâ€™s session. I strongly believe that from now on, you may be able to combine the **AGGREGATE** with the **IF** function in Excel. If you have any queries or recommendations, please share them in the comments section below.

## Related Articles

- AGGREGATE vs SUBTOTAL in Excel
- AGGREGATE Formula for Adding Serial Number in Excel
- How to Use Conditional AGGREGATE Function in Excel
- How to Combine INDEX and AGGREGATE Functions in Excel
- How to Aggregate COUNTIF in Excel

**<< Go Back to ****Excel AGGREGATE Function** **|** **Excel Functions** **|** **Learn Excel**