Table of Contents
When using formulas in Excel, the user can ignore blank cells by using the IFERROR and IF functions to check for empty cells and return a value if the cell is blank. This way, the formula will not return an error if the cell is empty and the user can still get the desired result.
You can use the following formulas in Excel to ignore blank cells when performing calculations:
Formula 1: Ignore Blank Cells in One Column
=IF(A2<>"", A2+10, "")
This particular formula adds 10 to the value in cell A2 only if the value in cell A2 is not blank.
Formula 2: Ignore Blank Cells in Multiple Columns
=IF(AND(A2<>"", B2<>""), A2+B2, "")
This particular formula adds the values in cells A2 and B2 only if both cells are not blank.
The following examples show how to use each formula in practice.
Example 1: Ignore Blank Cells in One Column
Suppose we have the following data in Excel that shows the points scored by various basketball players:
Now suppose we use the following formula to add 10 to each of the values in column A:
=A2+10
The following screenshot shows how to use this formula:
Notice that 10 is added to each cell in column A even if the cell in column A is blank.
Instead, we can use the following formula to add 10 to each of the cells in column A and ignore the blank cells entirely:
=IF(A2<>"", A2+10, "")
The following screenshot shows how to use this formula:
Notice that 10 is only added to each cell in column A that is not blank.
Example 2: Ignore Blank Cells in Multiple Columns
Suppose we have the following data in Excel that shows the points scored and rebounds collected by various basketball players:
We can use the following formula to add the values in the points and rebounds columns only for the rows where both values are not empty:
=IF(AND(A2<>"", B2<>""), A2+B2, "")
The following screenshot shows how to use this formula:
Notice that the values for points and rebounds are added only for the rows where both values are not blank.