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
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)“
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 )
