Common AGGREGATE questions usually fall into 3 areas: argument errors, unexpected results, and filling formulas across multiple cells.
FAQ
What should I do if AGGREGATE returns #VALUE!?
- Check whether the formula includes all required arguments, especially for forms that need an additional argument.
- Make sure the argument types are valid and that the numeric range does not contain incompatible text values.
- Confirm that you used English commas and matched parentheses correctly.
Why does the AGGREGATE result not match what I expected?
- Check whether the referenced range is correct and whether any cells were missed or selected by mistake.
- Review the
optionssetting to confirm whether hidden rows or error values should be ignored. - If needed, inspect the calculation step by step with formula evaluation tools.
How can I use AGGREGATE in multiple cells?
- Enter the full AGGREGATE formula in the first target cell.
- Drag the fill handle across the target range, or copy and paste the formula.
- After filling, check whether row or column references should be locked with
$.
Glossary
| Term | Definition |
|---|---|
| AGGREGATE | An aggregation function that can return statistics such as average, sum, or maximum from a data range, with options to ignore specific values. |
| function_num | The numeric argument that tells AGGREGATE which calculation to perform. |
| options | The argument that controls whether hidden rows, error values, or nested aggregates are ignored. |
| ref1 | The first referenced range or numeric argument used in the calculation. |
| Absolute reference | A reference that uses $ to lock a row number or column letter when a formula is copied. |