How to Average If Cell Is Not Blank in Google Sheets (5 Ways)

This article will guide you in cases where you may need to use google sheets average if the cell is not blank. Google Sheets includes functions to compute the average of a number in a range of cells and the average of cells based on particular parameters. Functions like AVERAGE and AVERAGEIF are two examples. However, there are occasions when the data in a cell is Blank, Text, or contains 0 (Zero), which may affect the results. This article will therefore assist you in efficiently handling them.

A Sample of Practice Spreadsheet

You can copy the spreadsheet that we’ve used to prepare this article.

5 Methods to Average If Cell Is Not Blank in Google Sheets

The AVERAGE function is one of the core functions available in Google Sheets. There are various uses for it, depending on the circumstance and requirements. Below, we’ll show possible scenarios.

1. Using the AVERAGE Function

Assume you have all of the results from a student who participated in two quizzes. You now wish to determine his overall score across the two tests. However, In Quiz 1, there is an empty cell in C7. We will apply the following steps.


  • Select cell C11.
  • We will use the following formula:
  • Press ENTER to see the result.

google sheets average if not blank first method

The AVERAGE function is going to ignore the blank cell and return a value of 12 by summing all the numbers and dividing them by 4.

In the second quiz, he was absent from the science quiz. We are going to use the same AVERAGE function.


  • Go to cell D11.
  • We will be using the following formula:
  • Again, press ENTER to get the result.

google sheets average if the cell contains a text value

Again, in this case, the function will not take into account the text in cell D6 and will instead return a value of 15, which is the average of the four subjects.

NOTE: AVERAGE function automatically ignores any cells that are blank or that contain text.

2. Applying AVERAGEA Function Taking Text Value as Zero

In the previous example, we can mark Absent as zero and find his overall score just by using the AVERAGEA function.


  • Select cell D11.
  • Write down the following formula:
  • Press ENTER to view to result.

use of averagea function when google sheets average if not blank

We can observe that his average fell from 15 to 12 when compared to the prior result. Therefore, while determining the average of any set of data, we can treat any text as zero using the AVERAGEA formula.

Read More: How to Average in Google Sheets If Value Is Not 0 (2 Simple Ways)

3. Utilizing AVERAGEIF Function with Criteria

The best formula to utilize in Google Sheets if a condition or criterion needs to be satisfied to calculate an average is AVERAGEIF. For a better understanding of the formula, we will provide two scenarios with examples.

3.1 AVERAGEIF for Not Blank Criteria

We are going with the same dataset as before, however, there will be a slight change. Let’s say that due to some reason, the history quiz was canceled and it wouldn’t be counted. Therefore, now we are going to average the subjects which are not blank in the subjects column.


  • Go to cell D11.
  • Use the following formula:
  • Press ENTER.

application of averageif functions in case of blank cells

As you can see, the function disregarded the number 20 in cell C7 and returned a value of 12, which is the average of the four subjects.

3.2 Ignoring Zero (0) While Averaging

Using the same database and formula we can also exclude the value 0 (zero). In Quiz 2, the student scored a zero. If we look at the previous examples, it will be in the History exam, which was canceled later. Therefore now we are going to count the average excluding the 0 using the following process.


  • Click cell D11.
  • Use the following formula:
  • Finally, press ENTER.

ignoring zero (0) while averaging using averageif function

We expect the Quiz 2 result to have an arithmetic mean of 12.8. Since the 0 in cell D7 has been disregarded, we are now working with the other four subjects. The total mark, 64, was then divided by 4 using the AVERAGEIF method.

Limitation: AVERAGEIF function does not count text value and automatically ignores it.

Read More: How to Use AVERAGEIF Function in Google Sheets (6 Examples)

4. Using AVERAGE and IF Functions

We are going to use a similar example again but with a slight difference in it. Assume that the student was absent in two quizzes, Science in the first and Foreign Language in the second. We are now going to calculate the average of the five subjects, nevertheless, the cell contains a blank cell or text. It will count the value as zero.


  • After closing the bracket, press ENTER.

use of averagea and if function while google sheet average if not blank

The ARRAYFORMULA is used to verify whether the name of each subject is not blank before only using the marks from subjects whose names are not blank in the AVERAGEA Function. The subject name is blank, hence the value 20 is ignored. The AVERAGEA Function treats the text “Absent” as 0, resulting in a calculation of an average of 7.2.

We’re going to apply the following formula on Quiz 2.


  • Go to cell C11.
  • Use the following formula:
  • Press ENTER to see the result.

count all the blank cells and text as Zero to get an average from any data

Same as before, this time there is a 0 in the cell D7, which is also counted as zero in the formula. Thus delivering an average of 9.2.

NOTE: Use this formula if you want to count all the blank cells and text as Zero to get an average from any data.

Read More: How to Find Average in Google Sheets (8 Easy Ways)

5. Applying IF, COUNT, and AVERAGE Formula

Only if any cell in the range is blank, can you use the following formula with a combination of IF, COUNT, and AVERAGE functions to determine the average value of a range in Google Sheets. Three choices are available, depending on the criterion. Such as:

5.1 Column Contains a Blank Cell

Assume for this example that the student did not participate in one subject during the first quiz and did not participate at all during the second quiz. You must, however, obtain the overall average of the student.


  • Select cell C11.
  • This time, we’ll calculate the average using the formula below.
  • To get your result, press ENTER.

application of if, count and average functions

Formula Explanation:

  • COUNT(C5:C9) is the logical_expression of the IF function.
  • If the value is TRUE or returns any numerical value, it will activate AVERAGE(C5:C9).
  • If it does not return any value or FALSE, it will show “0”.

We can see that in Quiz 1 Column, The formula gave us the average of the non-missing data which is 12. Despite the missing data, the formula simply counts the data-filled cells.

5.2 Return Zero (0) for Missing Values

Same example and formula as before but we will calculate an average for the Quiz 2 column.


  • Click cell C11.
  • Formula:
  • Press ENTER to finalize.

application of if, count and average functions to average blank cells

Formula Explanation:

  • COUNT(C5:C9) will act as the logic of the IF function.
  • AVERAGE(C5:C9) will activate If the value is TRUE or returns any numerical value.
  • 0, If it does not return any value or FALSE.

For all the missing values, however, it returned zero in the case of Quiz 2.

5.3 Return Blank for Missing Values

If you don’t want to have any numerical value for any blank cells, You can use the following formula to get a blank result.


  • Select cell D11.
  • Formula:
  • Press ENTER to view to result.

average if not blank return a blank result

Formula Explanation:

  • The IF function will use COUNT(C5:C9) as its logic.
  • If the value is TRUE, AVERAGE(C5:C9) will be activated.
  • “” represents a blank cell. In case of the formula not returning any value or FALSE, it will return the blank cell.

In the image above, it is clear that the formula returned a blank value as the average of all the blank cells in Quiz 2.

Final Words

Depending on your needs, you can employ any strategy you choose. In our daily lives, the average mathematical formula is crucial for determining something’s core value. When using Google Sheets, the dataset can be blank, text, or zero, among other things. Therefore, if you don’t know how to use it, finding an average if not blank, can be a hassle.
Visit our website, OfficeWheel, for more information.

Related Articles

Ishrak Khan

Ishrak Khan

Ishrak Khan is a writer and urban planner born in Dhaka, Bangladesh, one of the largest mega-cities. He is a professional Content Developer closely working with OfficeWheel. He simply likes to enthrall his readers. He loves Football, Nico Robin, Deserts, and Twix. If you like the post, give him a heart on Instagram.

We will be happy to hear your thoughts

Leave a reply