How to Ignore Blank Cells with Formulas in Google Sheets(Quick & Easy Guide)

In this tutorial, you will learn How to Ignore Blank Cells with Formulas in Google Sheets.

You can use the following formulas in Google Sheets to ignore blank values when performing calculations:

To ignore blank cells in Google Sheets during calculation you can use the below formulas.

Formula 1: Ignore blanks by Calculating SUM

The below formula will evaluate the sum of values for the range B2:B10 while ignoring the blank cells in the range A2:A10

=AVERAGEIF(A2:A10,"<>",B2:B10)

Formula 2: Ignore blanks by Calculating Average

EXCEL TIP INSERT BLANK COLUMNS AFTE...
EXCEL TIP INSERT BLANK COLUMNS AFTER EVERY DATA COLUMN IN EXCEL

The below formula will perform the average calculation for the range B2:B10 while ignoring the blank values in the range A2:A10

=SUMIF(A2:A10,"<>",B2:B10)

Let’s go through the above formulas in detail with examples.

How to Ignore Blank Cells with Formulas in Google Sheets: Step-by-Step Guide

Here’s How to Ignore Blank Cells with Formulas in Google Sheets

First Example: Ignore blanks by Calculating SUM

In the below example, we have a dataset for which we will calculate the SUM of points scored for the Points Column where the value in the team’s column is not empty

Enter the formula in the cell D2 “=SUMIF(A2:A10,”<>”,B2:B10)

How to Ignore Blank Cells with Formulas in Google Sheets

Now press enter

The calculated Sum of Points for the non-blank teams is 118

To verify the above-calculated value we can perform a manual calculation as shown below

We can verify this is correct by manually calculating the sum of the points values for the non-blank teams:

Sum of Points for Cells which are Not Blank: 24+24+20+20+12+10+8 = 118.

Second Example: Ignore blanks by Calculating Average

In the below example, we have a dataset for which we will calculate the Average of points scored for the Points Column where the value in the teams’ column is not empty

Enter the formula in the cell D2 =AVERAGEIF(A2:A10,”<>”,B2:B10)

Press enter.

To verify the above-calculated Average value we can perform an average manual calculation as shown below

We can verify this is correct by manually calculating the sum of the points values for the non-blank teams:

Average Calculation for Cells which are not Not Blank : 24+24+20+20+12+10+8/7 = 16.85

How to Ignore Blank Cells with Formulas in Google Sheets(Conclusion)

In the above tutorial, we have walked you through examples using which you can easily Ignore Blank Cells with Formulas in Google Sheets.

We hope this tutorial on How to Ignore Blank Cells with Formulas in Google Sheets was helpful.

Related articles :

How to Insert Spin Button in Google Sheets ( Easy Guide )

How to Freeze Rows in Google Sheets

How to Calculate Square Root and Cube Root in Google Sheets(Quick & Easy Guide)

How to use SUMSQ Function in Google Sheets(Quick & Easy Guide )