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.


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.


4 Suitable Examples of Combining AGGREGATE with IF Function in Excel

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 Office 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.

Combining AGGREGATE with IF Function in Excel to find highest and lowest profit

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.

Combining AGGREGATE with IF Function in Excel to find highest profit

  • 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$.

Combining AGGREGATE with IF Function in Excel to find lowest profit

🔎 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.

Combining AGGREGATE with IF Function to find highest marks

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.

show the output

🔎 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, 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 Conditional AGGREGATE Function in Excel (2 Methods)


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.

Combining AGGREGATE with IF Function to find lowest marks

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.

show the output

🔎 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.

Combining AGGREGATE with IF Function to find to find minimum values

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.

show the output

🔎 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, 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 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.

Read More: How to Aggregate Data in Excel (3 Easy Ways)


Conclusion

That’s the end of today’s session. I strongly believe that from now, 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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Saquib

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo