Handle #N/A in calculations
IntermediateWhen you pull data from lookups or external sources, some cells may contain #N/A errors. A regular SUM on these cells will return #N/A, which breaks your reports.
There are several ways to sum values while ignoring errors:
- AGGREGATE function - Has a built-in option to ignore error values
- SUMIF with error checking - Filter out errors before summing
- IFERROR wrapper - Replace each error with 0 before summing
The AGGREGATE function can perform various calculations (SUM, AVERAGE, COUNT, etc.) while optionally ignoring errors, hidden rows, or nested functions.
The syntax is:
=AGGREGATE(function_num, options, range)
Where function_num specifies the calculation (9 = SUM) and options controls what to ignore (6 = ignore error values).
Your task:
You have quarterly sales data for several products. Some quarters have #N/A errors where data was unavailable.
In cell C8, calculate the total Q2 Sales (column C) using a method that ignores the #N/A errors and returns the correct sum.
Need some help?
Hint 1
Try using AGGREGATE with function_num 9 (SUM) and options 6 (ignore errors).
Hint 2
The AGGREGATE syntax is: =AGGREGATE(9, 6, range) where 9 means SUM and 6 means ignore error values.
Hint 3
Your range should be C2:C6 to cover all the Q2 Sales values.
Handle #N/A in calculations
IntermediateWhen you pull data from lookups or external sources, some cells may contain #N/A errors. A regular SUM on these cells will return #N/A, which breaks your reports.
There are several ways to sum values while ignoring errors:
- AGGREGATE function - Has a built-in option to ignore error values
- SUMIF with error checking - Filter out errors before summing
- IFERROR wrapper - Replace each error with 0 before summing
The AGGREGATE function can perform various calculations (SUM, AVERAGE, COUNT, etc.) while optionally ignoring errors, hidden rows, or nested functions.
The syntax is:
=AGGREGATE(function_num, options, range)
Where function_num specifies the calculation (9 = SUM) and options controls what to ignore (6 = ignore error values).
Your task:
You have quarterly sales data for several products. Some quarters have #N/A errors where data was unavailable.
In cell C8, calculate the total Q2 Sales (column C) using a method that ignores the #N/A errors and returns the correct sum.
Need some help?
Hint 1
Try using AGGREGATE with function_num 9 (SUM) and options 6 (ignore errors).
Hint 2
The AGGREGATE syntax is: =AGGREGATE(9, 6, range) where 9 means SUM and 6 means ignore error values.
Hint 3
Your range should be C2:C6 to cover all the Q2 Sales values.