How to Use Conditional AGGREGATE Function in Excel

While working in Excel, we often need to use the AGGREGATE function. This article covers “Conditional AGGREGATE Function in Excel”. Basically, the phrase expresses two things. Either you want to apply the condition with the AGGREGATE function or you’re searching for some Excel functions that aggregate data regarding your specified condition. For your convenience, we’re going to demonstrate these two issues. Let’s explore the article with utmost attention!

2 Methods to Use Conditional AGGREGATE Function in Excel

In this section of the article, we will learn 2 simple methods to use the conditional AGGREGATE function in Excel. Let’s say, we have Marks of 8th Grade Students as our dataset. This dataset contains Marks from 3 Tests for 4 8th Grade Students.

1. Using AGGREGATE Function to Find Lowest Value

The main purpose of the conditional AGGREGATE function is to aggregate a set of data of a certain dataset, based on one or more specified conditions. Using the conditional AGGREGATE function often gives us an upper hand while dealing with large datasets.

In the first method, we will use the conditional AGGREGATE function of Excel to calculate the Lowest Marks of a Student among 3 Tests. Let’s follow the steps mentioned below to do this.

Step 01: Create Drop-down Button to Select Name

Firstly, create a table in your worksheet as shown in the following image.

Following that, select the cell where you want to display the Name. In this case, we selected cell C18.
Then, go to the Data tab from Ribbon.
Now, select the Data Validation option from the Data Tools group.

As a result, the Data Validation dialogue box will open on your worksheet.

Now, in the Data Validation dialogue box, select the List option.
After that, in the Source field type the Names of the 4 students with a comma as a separator between the Names.
Next, click on OK.

Consequently, the drop-down button will be added beside cell C18. You will get the list of Names shown in the image below after clicking on the drop-down button.

Step 02: Apply AGGREGATE Function to Find Lowest Value

Firstly, enter the following formula in cell C19.

=AGGREGATE(15,6,(D5:D16)/(B5:B16=C18),1)

Here, the range of cells D5:D16 refers to the cells of the Marks column, the range B5:B16 represents the cells of the Name column, and the cell C18 indicates the selected Name from the drop-down list.

Formula Breakdown

AGGREGATE(15,6,(D5:D16)/(B5:B16=C18),1) → It returns a value based on the specified function and conditions.
- 15 → It is the function_num argument. By this number, the SMALL function of Excel is specified.
- 6 → This refers to the options argument. This number represents the Ignore error values option.
- (D5:D16)/(B5:B16=C18) → It is the array argument.
  - Output → {75;#DIV/0!;#DIV/0!;#DIV/0!;71;#DIV/0!;#DIV/0!;#DIV/0!;87;#DIV/0!;#DIV/0!;#DIV/0!}
- 1 → This is the [k] argument. It is an optional argument.
Output → 71.

Following that, press ENTER.

Consequently, you will have the Lowest Marks of Jocelyn among her 3 Tests as demonstrated in the following picture.

You can also choose a different Name from the drop-down button and your output will be adjusted automatically. For instance, we selected Name Asher in cell C18. As a result, our output changed to 79 which is the Lowest Marks for Asher among her 3 Tests.

2. Utilizing AGGREGATE Function to Find Highest Value

In this method, we will use the conditional AGGREGATE function to find the Highest Marks of a Student among the 3 Tests. Let’s use the procedure discussed in the following section.

Steps:

Firstly, use the steps mentioned in Step 01 of the 1st method to enable the drop-down button beside cell C18 as shown in the image below.

After that, use the following formula in cell C19.

=AGGREGATE(14,6,(D5:D16)/(B5:B16=C18),1)

Formula Breakdown

AGGREGATE(14,6,(D5:D16)/(B5:B16=C18),1) → It gives a value based on the specified function and conditions.
- 14 → It is the function_num argument. By this number, the LARGE function of Excel is declared.
- 6 → This refers to the options argument. This number represents the Ignore error values option.
- (D5:D16)/(B5:B16=C18) → It is the array argument.
  - Output → {#DIV/0!;#DIV/0!;70;#DIV/0!;#DIV/0!;#DIV/0!;74;#DIV/0!;#DIV/0!;#DIV/0!;80;#DIV/0!}
- 1 → This is the [k] argument. It is an optional argument.
Output → 80.

Now, hit ENTER.

As a result, you will get the Highest Marks of Xavier among his 3 Tests as marked in the following image.

You can choose a different Name from the drop-down list and the output will change accordingly. For example, we selected Nathan from the drop-down list and our output changed to 95 which is the Highest Marks for Nathan among his 3 Tests.

Exploring Some Excel Functions to Aggregate Data Based on Condition

There are some functions in Excel by which we can replicate the function of the conditional AGGREGATE function. Among them, the SUMIF and the COUNTIF functions are the most common ones. Let’s say, we have Marks of 6th Grade Students of 2 Sections as our dataset.

1. Employing SUMIF Function to Aggregate Data

In this example, we will calculate the Sum Value of the Marks of 6th Grade Students based on their Section. Let’s follow the steps mentioned below to do this.

Step 01: Create Drop-down Button to Select Section

Firstly, create a table as shown in the following picture.

After that, select the cell where you want to display the Section. Here, we selected cell C14.
Then, go to the Data tab from Ribbon.
Now, click on the Data Validation option from the Data Tools group.

Following that, in the Data Validation dialogue box, choose the List option.
Then, in the field named Source, type the Name of the Sections as shown in the following picture.
Next, click on OK.

As a result, the drop-down button will be added beside cell C14. You will have the name of the Sections after clicking on the drop-down button.

Step 02: Apply SUMIF Function to Calculate Sum Value

Firstly, enter the following formula in cell C15.

=SUMIF(C5:C12,C14,D5:D12)

Here, the range of cell C5:C12 represents the cells of the Section column, range D5:D12 refers to the cells of the Marks column, and cell C14 indicates the selected Section. Now, the SUMIF function will return the sum from the sum_range D5:D12 depending upon the criteria C14 from the criteria range C5:C12.

Then, press ENTER.

Consequently, you will have the Sum Value of Marks of students of Section A as demonstrated in the image given below.

If you select Section B in the drop-down list, the Sum Value will be changed automatically to 344.

2. Applying COUNTIF Function to Aggregate Data Based on Condition

Now, we will use the COUNTIF function to mimic the conditional AGGREGATE function of Excel to find the Number of Students Having Marks Greater Than 80. Let’s use the procedure discussed in the following section to do this.

Steps:

Firstly, create a table in your worksheet as shown in the following image.

After that, enter the formula given below in cell D14.

=COUNTIF(D5:D12,">=80")

Here, the COUNTIF function will return a count of cells that have a value greater or equal to 80 from the range D5:D12.

Then, press ENTER.

Consequently, you will have the Number of Students Having Marks Greater Than 80 as demonstrated in the image below.

Read More: How to Aggregate COUNTIF in Excel

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

Download Practice Workbook

Conditional AGGREGATE Function.xlsx

Conclusion

That’s all about today’s session. I strongly believe that this article was able to guide you to use the conditional AGGREGATE function in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality.

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

Get FREE Advanced Excel Exercises with Solutions!