How to Use AGGREGATE to Achieve MAX IF Behavior in Excel

Microsoft Excel is a powerful software. There are many default Excel Functions that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Sometimes, we need to find the maximum value based on certain conditions. We can do that by combining the MAX and the IF functions. Moreover, the MAXIFS function can also do the job. However, the AGGREGATE function can perform a similar task with some additional benefits like ignoring error values. This article will show you 3 ideal examples to use AGGREGATE to achieve MAX IF behavior in Excel.


Download Practice Workbook

Download the following workbook to practice by yourself.


3 Ideal Examples to Use AGGREGATE to Achieve MAX IF Behavior in Excel

The AGGREGATE function can carry out various operations depending on the Function Number that we’ll input in the argument. Then, we can decide on what to ignore by choosing the Option Number. Subsequently, we have to select the Array on which we’ll perform the operations. Here, we’ll show how you can use this function to achieve the MAX IF behavior. To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset contains Region, Product, Net Sales, & Invoice Date. This article will show the process to extract the maximum sales of a product from a particular region.

excel aggregate max if


1. Use AGGREGATE Function for MAX IF to Find Last Date in Excel

In our first example, we’ll determine the last invoice date for the product TV. The last date will have the maximum value in terms of numbers. In this regard, we’ll input 14 as the function number. 14 is for the LARGE function. If you select function number 4 which is MAX, you’ll get an error. Because it can’t calculate based on conditions. Therefore, follow the steps below to perform the task.

STEPS:

  • First, select cell D12.
  • Then, type the formula:
=AGGREGATE(
  • As a result, you’ll see the function numbers along with the function names.
  • Double-click the LARGE function.

Use AGGREGATE Function for MAX IF to Find Last Date in Excel

  • Consequently, choose the option number which suits your requirement.
  • In this example, we choose 6 (Ignore error values).

  • Now, type the array:
(E5:E10)/(C5:C10=C5)
  • Finally, the formula is:
=AGGREGATE(14,6,(E5:E10)/(C5:C10=C5),1)
  • Next, press Enter to get the output.
  • Thus, you’ll get the last invoice date for TV.

NOTE: In the array section, E5:E10 is the Net Sales range. And C5:C10=C5 looks for the product TV. 1 is for the 1st largest number.

2. Determine Maximum Based on Criteria with Excel AGGREGATE Function

Now, we’ll show how you can get the maximum Net Sales of TV. However, instead of using cell range in the array argument, we’ll input table reference. You can easily create a table by pressing the Ctrl and T keys together after selecting the desired range. In this example, the condition is TV. So, learn the following steps to determine maximum sales based on single criteria with the Excel AGGREGATE function. We’ll use 14 as the function number and 6 as the option number for this example also.

STEPS:

  • Firstly, we create a table (Table1) by selecting the range B4:E10.
  • See the picture below where we have the table.

Determine Maximum Based on Criteria with Excel AGGREGATE Function

  • After that, input the formula:
=AGGREGATE(14,6,Table1[
  • Hence, the table headers will pop out.
  • Choose Net Sales.

  • Afterward, type /Table1[ and table headers will appear again.
  • There, select Product.

  • Lastly, the whole formula is:
=AGGREGATE(14,6,Table1[Net Sales]/(Table1[Product]="TV"),1)
  • Next, press Enter.
  • Consequently, you’ll get an accurate result.

NOTE: Table1[Product]=”TV” looks for TV in the Product range. And 1 is for the 1st maximum value.

3. Insert AGGREGATE to Achieve Maximum Based on Double Criteria

However, we may face real-life problems where we have to find out the maximum value from a range based on multiple conditions. In our last example, we’ll demonstrate how you can do that with the AGGREGATE function. The conditions are West and TV as the Region and the Product respectively. Hence, follow the process below.

STEPS:

  • First of all, click cell D12.
  • Then, type the formula:
=AGGREGATE(14,6,(B5:B10=B5)
  • Here, B5:B10=B5 matches the first condition which is West as Region.

Insert AGGREGATE to Achieve Maximum Based on Double Criteria

  • Next, type *(C5:C10=C5), and the formula becomes:
=AGGREGATE(14,6,(B5:B10=B5)*(C5:C10=C5)
  • Subsequently, this part looks for the product TV.

  • Finally, put *D5:D10,1 and the complete formula is:
=AGGREGATE(14,6,(B5:B10=B5)*(C5:C10=C5)*D5:D10,1)
  • Press Enter.
  • In this way, it’ll return the maximum value based on double criteria.

NOTE: D5:D10 is the Net Sales range from which we’ll extract the maximum value. 1 is for the 1st largest value.

Conclusion

Henceforth, you will be able to use AGGREGATE to achieve MAX IF behavior in Excel following the above-described examples. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo