Method 1 – Finding the Highest and Lowest Profit Based on Sales
In this method, weâ€™ll demonstrate how to combine the AGGREGATE and IF functions using greater than and less than criteria. For illustrative purposes, weâ€™ll apply these functions to the following dataset, where we want to identify profits based on sales greater or less than $2000.
Follow these steps:
 Select the cell where you want to display the result (e.g., cell G5).
 Enter the following formula:
=AGGREGATE(14,6,IF(C5:C15>2000,D5:D15),1)
 Press Enter.
 This will give you the maximum profit based on sales greater than $2000.
 Select cell G6 and enter the following formula:
=AGGREGATE(14,6,IF(C5:C15<2000,D5:D15),1)
 Press Enter.
 This will give you the maximum profit based on sales less than $2000.
 Select the cell where you want to display the result (e.g., cell G9).
 Enter the following formula:
=AGGREGATE(15,6,IF(C5:C15>2000,D5:D15),1)
 Press Enter.
 This will give you the minimum profit based on sales greater than $2000.
 Select another cell (G10) and enter the following formula:
=AGGREGATE(15,6,IF(C5:C15<2000,D5:D15),1)
 Press Enter.
 This will give you the lowest profit based on sales less than $2000.
How Does the Formula Work?
 AGGREGATE(14, 6, IF(C5:C15 > 2000, D5:D15), 1):
 The IF function checks whether the values in the range C5:C15 are greater than 2000. If true, it collects corresponding values from D5:D15.
 The AGGREGATE function then returns the largest value (1st largest) from the collected values.
 Inside the parentheses:
 14 represents the LARGE function.
 6 means we ignore error values.
 1 specifies the 1st largest value.
 AGGREGATE(14, 6, IF(C5:C15 < 2000, D5:D15), 1):
 Similar to the previous case, but it collects smaller values based on the IF condition.
 AGGREGATE(15, 6, IF(C5:C15 > 2000, D5:D15), 1):
 Collects the largest value based on sales greater than $2000.
 AGGREGATE(15, 6, IF(C5:C15 < 2000, D5:D15), 1):
 Collects the smallest value based on sales less than $2000.
Method 2 – Finding the Highest Marks in a Group
In this method, weâ€™ll demonstrate how to combine the AGGREGATE and IF functions in Excel. Weâ€™ll apply these functions to the following dataset to identify the highest marks based on groups not equal to 3 or 5.
Follow these steps:
 Select the cell where you want to display the result (e.g., cell F5).
 Enter the following formula:
=AGGREGATE(14,6,IF(C5:C15=3,B5:B15),1)
 Press Enter.
 This will give you the maximum marks based on groups not equal to 3.
 Select cell F6 and enter the following formula:
=AGGREGATE(14,6,IF(C5:C15=5,B5:B15),1)
 Press Enter.
 This will give you the maximum marks based on groups not equal to 5.
How Does the Formula Work?
 AGGREGATE(14, 6, IF(C5:C15 = 3, B5:B15), 1):
 The IF function checks whether the values in the range C5:C15 are equal to 3. If true, it collects corresponding values from B5:B15.
 The AGGREGATE function then returns the largest value (1st largest) from the collected values.
 Inside the parentheses:
 14 represents the LARGE function.
 6 means we ignore error values.
 1 specifies the 1st largest value.
 AGGREGATE(14, 6, IF(C5:C15 = 5, B5:B15), 1):
 Similar to the previous case, but it collects larger values based on the IF condition.
Read More: How to Aggregate Data in Excel
Method 3 – Finding the Lowest Marks in a Group
In this method, weâ€™ll illustrate how to find the lowest marks based on groups not equal to 3 or 5.
Follow these steps:
 Select the cell where you want to display the result (e.g., cell F5).
 Enter the following formula:
Â =AGGREGATE(15,6,IF(C5:C15=3,B5:B15),1)
 Press Enter.
 This will give you the minimum marks based on groups not equal to 3.
 Select cell F6 and enter the following formula:
=AGGREGATE(15,6,IF(C5:C15=5,B5:B15),1)
 Press Enter.
 This will give you the minimum marks based on groups not equal to 5.
How Does the Formula Work?
 AGGREGATE(15, 6, IF(C5:C15 = 3, B5:B15), 1):
 The IF function checks whether the values in the range C5:C15 are equal to 3. If true, it collects corresponding values from B5:B15.
 The AGGREGATE function then returns the smallest value (1st smallest) from the collected values.
 Inside the parentheses:
 15 represents the SMALL function.
 6 means we ignore error values.
 1 specifies the 1st smallest value.
 AGGREGATE(15, 6, IF(C5:C15 = 5, B5:B15), 1):
 Similar to the previous case, but it collects smaller values based on the IF condition.
Read More: How to Use Excel AGGREGATE Function with Multiple Criteria
Method 4 – Adding Multiple AGGREGATEIF Formulas
Letâ€™s break down the steps for combining multiple AGGREGATEIF formulas in Excel to find the lowest marks based on groups not equal to 3 and 5.
Follow these steps:
 Select the cell where you want to display the result (e.g., cell F5).
 Enter the following formula:
=AGGREGATE(15,6,IF(C5:C15=3,B5:B15),1)+AGGREGATE(15,6,IF(C5:C15=5,B5:B15),1)
 Press Enter.
 This will give you the minimum marks based on the group not 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)

 AGGREGATE(15, 6, IF(C5:C15 = 3, B5:B15), 1):
 The IF function checks whether the values in the range C5:C15 are equal to 3. If true, it collects corresponding values from B5:B15.
 The AGGREGATE function then returns the smallest value (1st smallest) from the collected values.
 Inside the parentheses:
 15 represents the SMALL function.
 6 means we ignore error values.
 1 specifies the 1st smallest value.
 AGGREGATE(15, 6, IF(C5:C15 = 5, B5:B15), 1):
 Similar to the previous case, but it collects smaller values based on the IF condition.
In the final stage, the sum of both AGGREGATE functions gives the desired output (in this case, 52).
 AGGREGATE(15, 6, IF(C5:C15 = 3, B5:B15), 1):
Download Practice Workbook
You can download the practice workbook from here:
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
Get FREE Advanced Excel Exercises with Solutions!