Google Sheets: How to Ignore Blank Cells with Formulas


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:

The sum of the points values for the teams that aren’t blank is 130.

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.

Additional Resources

The following tutorials explain how to perform other common operations in Google Sheets:

Google Sheets: How to Sum Cells If Not Blank
Google Sheets: How to Replace Blank Cells with Zero

Leave a Reply

Your email address will not be published.