How to Ignore Blank Cells When Using Formulas in Excel


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.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Replace #N/A Values in Excel
How to Ignore #N/A Values When Using Formulas in Excel

Leave a Reply

Your email address will not be published.