Sum a Column Ignoring Formula Errors

0 Flares Filament.io 0 Flares ×

Having formula errors on a spreadsheet can be bad news. They look ugly, they frighten the less Excel experienced among us and they stop our other formulas from working.

If you have formula errors on a spreadsheet it is normally best to stop it at its source. To either correct the error, or to hide it using formulas such as ISERROR and IFERROR.

However, if your spreadsheet is large, having these IFERROR functions in every cell to protect against error values will add more calculation time to your spreadsheet.

There is a function in Excel called AGGREGATE which allows us to perform various functions on a range whilst ignoring formula errors.

Using AGGREGATE to Ignore Error Values

The worksheet below contains formula errors that we want to ignore when calculating the total investment.

Sum values whilst ignoring formula errors

The following formula can be written into cell E17.

Aggregate function to ignore error values

There is a lot more to the AGGREGATE function. For example, it can also be used to ignore hidden rows and subtotals. And it can perform 19 different functions in all so is not limited to only summing values.

Watch the video below to see the AGGREGATE function being used to sum values whilst ignoring error messages.

One thought on “Sum a Column Ignoring Formula Errors

Leave a Reply

Your email address will not be published. Required fields are marked *