How to Use Conditional AGGREGATE Function in Excel (2 Methods)

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!


Download Practice Workbook


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.

conditional aggregate function excel


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.

Using AGGREGATE Function to Find Lowest Value in Excel

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

Create Drop-down Button to Select Name to use the conditional AGGREGATE function in Excel

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.

Final output of step 1 of method 1 to use the conditional AGGREGATE function in Excel

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.

Final output of step 2 of method 1 to use the conditional AGGREGATE function in Excel

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


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:

Utilizing AGGREGATE Function to Find Highest Value in Excel

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

Final output of method 2 to use the conditional AGGREGATE function in Excel

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


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.

Employing SUMIF Function to Aggregate Data in Excel


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.

Create Drop-down Button to Select Section  to use the conditional AGGREGATE function in Excel

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

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


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.

Applying COUNTIF Function to Aggregate Data Based on Condition in Excel

  • 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: AGGREGATE vs SUBTOTAL in Excel (4 Differences)


Practice Section

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

Practice section to use the conditional AGGREGATE function in Excel


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. To learn more about Excel, you can visit our website, ExcelDemy. Happy learning!


Related Articles

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo