Combining AGGREGATE with IF Function in Excel (4 Examples)

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.

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)

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

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

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

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)

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.

AGGREGATE with IF Function.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!

Combining AGGREGATE with IF Function in Excel (4 Examples)

Combining AGGREGATE with IF Function in Excel: 4 Examples

1. Finding Highest and Lowest Profit Based on Sales

2. Finding Highest Marks in a Group

3. Finding Lowest Marks in a Group

4. Adding Multiple AGGREGATE-IF Formula

Conclusion

Related Articles

Leave a reply Cancel reply