Table of Contents
To make Google Sheets ignore blank cells with formulas, you can use the IF and ISBLANK functions together to check whether a cell is blank or not and return a specific value. This value can then be used in any other formula to achieve the desired result. For example, using the IF and ISBLANK functions together can help you sum only the cells that contain numbers and ignore blank cells.
You can use the following formulas in Google Sheets to ignore blank values when performing calculations:
Formula 1: Calculate Average & Ignore Blanks
=AVERAGEIF(A2:A13,"<>",B2:B13)
This formula calculates the average value in the range B2:B13 only for the cells where A2:A13 is not blank.
Formula 2: Calculate Sum & Ignore Blanks
=SUMIF(A2:A13,"<>",B2:B13)
This formula calculates the sum of values in the range B2:B13 only for the cells where A2:A13 is not blank.
The following examples show how to use these formulas in practice.
Example 1: Calculate Average & Ignore Blanks
The following screenshot shows how to calculate the average value in the Points column only for the cells where the value in the Team column is not blank:
The average points value for the teams that aren’t blank is 16.25.
We can verify this is correct by manually calculating the average points value for the non-blank teams:
Average Points for Non-Blank Teams: (14+19+15+5+12+14+32+19) / 8 = 16.25.
Example 2: Calculate Sum & Ignore Blanks
The following screenshot shows how to calculate the sum of values in the Points column only for the cells where the value in the Team column is not blank:
We can verify this is correct by manually calculating the sum of the points values for the non-blank teams:
Sum of Points for Non-Blank Teams: 14+19+15+5+12+14+32+19 = 130.
The following tutorials explain how to perform other common operations in Google Sheets: