Functions Excel like SUM, COUNT, LARGE and MAX don’t work if there are error cells in the range. However, you can easily use the AGGREGATE function to get around this.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
How to use the Aggregate function in Excel
1. For example, Excel returns an error if you use the SUM function to sum a range of cells with an error.
2. Use the AGGREGATE function to sum a range with errors.
Explanation: the first parameter (9) tells Excel that you want to use the SUM function. The second parameter (6) tells Excel that you want to ignore error values.
3. The AutoComplete feature in Excel will help you find the above parameters.
4. The AGGREGATE function below finds the second largest number in an error range.
Explanation: the first parameter (14) tells Excel that you want to use the LARGE function. The second parameter (6) tells Excel that you want to ignore error values. The fourth parameter (2) tells Excel that you want to find the second largest number.
5. The AGGREGATE function below finds the maximum value ignoring error values and hidden rows.
Explanation: The first parameter (4) tells Excel that you want to use the MAX function. The second parameter (7) tells Excel that you want to ignore hidden row and error values. In this example, row 2 is hidden.
