How To Use the Aggregate Function in Microsoft Excel

By Indeed Editorial Team

Published March 25, 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Microsoft Excel is a powerful tool that you can use to help automate complex calculations and keep track of important information. Before using Excel, it can be helpful to learn about the variety of different formulas and commands that you can use to maximize your efficiency. The aggregate function in Excel is a useful formula that you can use to perform calculations while ignoring any cells that may cause issues with your formula.

In this article, we discuss what the aggregate function is, when to use it, how to use it and provide examples of what a calculation using the aggregate function might look like.

Related: How To Add a Target Line in Excel (Using Two Different Methods)

What is the aggregate function in Excel?

The aggregate function is a formula that you can use in Excel to perform calculations while avoiding potential errors. You can write the aggregate function with two different syntax options, either as a reference or an array. The syntax for the aggregate function when written as a reference is:
AGGREGATE(function,options,ref)

The syntax for the aggregate function when written as an array is:
AGGREGATE(function,options,array,[optional_argument]

In these formulas, the "function" variable is always a number between 1-19. When writing your function, the number that you write in the "function" spot determines what calculation your formula performs. Here are the numbers you can use and what calculation they make the formula perform:

  1. AVERAGE: This function instructs the formula to average the cells that you reference. If you write "1" as the function variable, your aggregate function will return the average of all the numbers in the cells that you include.

  2. COUNT: The COUNT function counts all of the values or cells that contain numbers.

  3. COUNTA: The COUNTA function performs the same calculation as COUNT, but instead of only counting cells that contain numbers, COUNTA counts the number of cells that aren't empty.

  4. MAX: The MAX function returns the largest value in your reference. This can be a useful function if you're trying to find the upper limit of your data.

  5. MIN: The MIN function returns the smallest value in your reference. Similar to the MAX function, this can be a useful function if you're trying to find the lower limit of your data.

  6. PRODUCT: This function gives you the product of all of the values in your reference.

  7. STDEV.S: This function estimates the standard deviation of your values based on a sample. This can be useful if you're trying to find the average but want to know about the range of numbers in your cells.

  8. STDEV.P: This function is similar to STDEV.S, but instead of estimating the standard deviation based on a sample, it uses a population.

  9. SUM: The SUM function calculates the sum of all the values in your cells.

  10. VAR.S: VAR.S gives you the variance based on a sample of your data.

  11. VARP.P: This function gives you variance based on a population, rather than a sample.

  12. MEDIAN: The MEDIAN function returns the value that is in the middle of your data. This function might be useful when combined with the AVERAGE function so that you can find the median as well as the mean.

  13. MODE.SNGL: This function tells you what number occurs the most frequently in your data. This can be helpful if you're trying to find the most common data point in your information.

  14. LARGE: The LARGE function returns the nth largest value, such as fifth or ninth.

  15. SMALL: SMALL returns the nth smallest value.

  16. PERCENTILE.INC: This function gives you the nth percentile of all the values in a range which include the value itself.

  17. QUARTILE.INC: The QUARTILE.INC function returns the quartile based on a percentile that includes the value.

  18. PERCENTILE.EXC: This function is similar to PERCENTILE.INC, but rather than including the value, this function excludes it.

  19. QUARTILE.EXC: QUARTILE.EXC is similar to QUARTILE.INC but returns the quartile based on a percentile that excludes the value.

When writing your formula, after you've chosen what function to include, you can then move on to the possible options that you can use as parameters in your formula. Similar to how the functions work, you can write the "options" variable as a number between 1 and 7. Here are all the possible options that you can use in your aggregate function in Excel, and what they do:

  1. Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions

  2. Ignore error values, nested SUBTOTAL and AGGREGATE functions

  3. Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions

  4. Ignore nothing

  5. Ignore hidden rows

  6. Ignore error values

  7. Ignore hidden rows and error values

The final variable for you to fill in when writing your formula is either the reference or array, depending on which syntax you chose. When filling in the reference, simply include all of the cells that you want the equation to reference. While this may seem like a lot to remember when writing your formula, Excel provides a drop-down menu that allows you to select the options from a list rather than remembering what each variable stands for.

Related: How To Calculate Range in Excel (Plus Real-World Examples)

When to use the aggregate function in Excel

The aggregate function can be useful in a variety of situations. If you're performing calculations using a set of data but need the formula to ignore certain cells or potential error messages, then you may want to consider using the aggregate function to perform the calculations for you. Here are some situations where you may want to use the aggregate function:

When analyzing data

One of the most common situations when you might want to use the aggregate function in Excel is when you're performing an analysis on a set of data. Your data may be the accounting numbers for a company or the results of an experiment, but if you need to find an equation that can help you analyze trends, then the aggregate function may be useful. This function can be especially useful if your data table is formatted in a way that's producing errors or leaving some values hidden.

Related: How To Merge Columns in Excel (With Methods and Tips)

When you need to ignore empty rows

If you have a data table that has multiple empty rows, then the aggregate function may help you. This function can perform calculations that ignore empty rows so that you don't get an error message when executing the formula. If you need to perform calculations but cannot reformat the data table, then the aggregate function may be a useful tool to try.

When trying to find the range of your data

If you have a range of data and need to find out the largest and smallest value, then the aggregate function may be helpful. By using the LARGE function, your formula can tell you what the highest number is in your data table. Conversely, the SMALL function can provide the smallest value. By using these functions together, you can easily figure out what the range of values in your data table is.

When trying to find the average value in your data table

If you need to find the average value of the numbers in your data, then the aggregate function may be able to help. If you need to find the average, then you can use the AVERAGE function that's available as a subsection of the aggregate function. This equation can add up all of the values that you provide and return the average value.

Related: How To Use the Match Function in Excel in 4 Steps (With Examples)

How to use the aggregate function in Excel

If you want to use the aggregate function in Excel, it may be helpful for you to read about how to incorporate the formula into your spreadsheet. Regardless of what calculations you want to perform, setting up the formula can be easy once you understand how it works. Here are the steps for using the aggregate function in Excel:

1. Create your data table or array

The first step when using the aggregate function is to make sure that all of your data is present on your spreadsheet. To do this, you may want to import the data file or enter the values into the cells manually. Once you've entered all of your data, you may want to label the rows and columns so that you and others can easily see where the values come from.

Related: View vs. Table in SQL: Definitions, Similarities and Differences

2. Decide what cell you want to contain the formula and enter it into the formula bar

Once you've entered your data into the spreadsheet, the next step is to decide where you want to your formula to go. Wherever you place the formula is where the results of the equation will show up, so it may be helpful to label the cell with information so that others can understand what it represents. Once you've selected the cell, you can write the equation as:

AGGREGATE(function,options,ref)

3. Replace the variables in the formula

After writing the equation, the next step is to replace the variables with the numbers that represent the functions and options. For example, if you wanted to find the median number in your data, you would replace "function" with 12. You would do the same thing for the "options" and "ref" variables. When entering the formula, Excel prompts you with a drop-down menu so that you can select the numbers manually, rather than needing to remember what each one represents.

4. Test your function to make sure that it works

Once you've replaced all the variables with the numbers and information that you want to analyze, the final step is to make sure your formula works. You can do this by checking the number in the cell that references the formula bar. If your formula is returning an error, you may want to go back and make sure that your syntax is correct.

Related: 10 Common Excel Formula Errors (And How To Fix Them)

Example of the aggregate function

If you're unsure what the formula should look like, it may help to read an example that shows how to correctly use the aggregate function. Using the function can be easy once you've gained some experience and understand how it interacts with the rest of your spreadsheet. Here's an example of how to use the function:

Suppose you have a data table that ranges from cells 2-9 in the A column and 2-9 in the B column but has error values in cells in A5 and B6. To start, enter the formula AGGREGATE(function,options,ref) and begin by replacing the variables. First, you can replace "function" with "1" as that number represents the AVERAGE function. Next, replace "options" with "6" as that number tells the function to ignore error values. Finally, replace "ref" with "A2:A:9,B2:B9" as this is the syntax for referencing multiple cells when using a formula in Excel.

For this example, your final equation should look like this: AGGREGATE(1,6,A2:A9,B2:B9). This equation will output the average value of all the numbers in rows 2-9 of the A and B columns while ignoring any error values that are present in your table.

Please note that none of the companies mentioned in this article are affiliated with Indeed.

Explore more articles