How to Ignore Blank Cells with Formulas in Google Sheets

To ignore blank cells with formulas in Google Sheets, you can use a combination of functions like IF, ISBLANK, ARRAYFORMULA, and FILTER. These functions help you handle empty cells and perform calculations only on non-empty cells. Here are a few examples:

Example 1: Average of non-empty cells

Suppose you have a range of values in cells A1:A10, and some of them are blank. To calculate the average of only the non-empty cells, you can use the AVERAGEIF function:

=AVERAGEIF(A1:A10, "<>") 

This formula calculates the average of the range A1:A10, considering only the cells that are not empty.

Example 2: Sum of non-empty cells

To calculate the sum of non-empty cells in a range, you can use the SUMIF function:

=SUMIF(A1:A10, "<>")

This formula sums up the values in the range A1:A10, but only for the cells that are not empty.

Example 3: Count of non-empty cells

If you want to count the number of non-empty cells in a range, use the COUNTA function:

=COUNTA(A1:A10)

This formula counts the number of non-empty cells in the range A1:A10.

Example 4: Using IF and ISBLANK to ignore blank cells

In cases where you need to apply more complex formulas, you can use a combination of the IF and ISBLANK functions. For example, suppose you want to apply a formula to the values in column A, but only if the corresponding cell in column B is not empty. You can use the following formula:

=IF(ISBLANK(B1), "", YOUR_FORMULA(A1))

Replace "YOUR_FORMULA" with the actual formula you want to apply. This formula checks if the cell B1 is empty using the ISBLANK function, and if it's empty, it returns an empty string; otherwise, it applies the formula to the value in cell A1.

Example 5: Using ARRAYFORMULA and FILTER to ignore blank cells

If you want to apply a formula to an entire range and ignore blank cells, you can use a combination of ARRAYFORMULA and FILTER functions. For example, suppose you want to multiply the values in column A by 2, but only for the non-empty cells in column B:

=ARRAYFORMULA(IFERROR(FILTER(A1:A10 * 2, B1:B10 <> ""), ""))

This formula first filters the range A1:A10 based on the condition that the corresponding cells in column B are not empty. Then, it multiplies the filtered values by 2 using ARRAYFORMULA. The IFERROR function is used to return an empty string for the cells that don't meet the condition.