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 theargument. By this number, the*function_num***SMALL function**of Excel is specified.**6**→ This refers to theargument. This number represents the*options***Ignore error values**option.**(D5:D16)/(B5:B16=C18)**→ It is theargument.*array***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 theargument. It is an*[k]***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*.

**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:__

- 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 theargument. By this number,*function_num***the LARGE function**of Excel is declared.**6**→ This refers to theargument. This number represents the*options***Ignore error values**option.**(D5:D16)/(B5:B16=C18)**→ It is theargument.*array***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 theargument. It is an*[k]***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.*

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

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

**from the criteria**

*criteria*C14**.**

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

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

## Related Articles

- How to Aggregate Data in Excel
- AGGREGATE Formula for Adding Serial Number in Excel
- Combining AGGREGATE with IF Function in Excel
- How to Combine INDEX and AGGREGATE Functions in Excel
- AGGREGATE vs SUBTOTAL in Excel

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