In this article:

Average IF Not Blank Google Sheets (Easiest Way in 2024)

In this article we will show how to AVERAGE IF not blank in Google Sheets by using the AVERAGEIF and AVERAGEIFS functions combined with the not equal to operator <>. Simply follow the steps below:

For the purpose of the following demonstrations an example sheet was created. Access this and follow along by clicking here.

Average IF Not Blank Using the AVERAGEIF Function

In the demonstration below we will use the Average IF function on one and two data sets, not including blank cells in the calculations.

1. Select an empty cell in your spreadsheet 

Click an empty cell in your sheet, this will be the cell the results populate in.

In our example we have highlighted cell D5.

How to use Average IF in Google Sheets: Selecting a Cell

2. Enter the formula =AVERAGEIF(CellRange,"<>")

In the empty cell input the formula in the format of =AVERAGEIF(CellRange,"<>")

Formula Breakdown:

=AVERAGEIF: A function that returns the average of a range of data depending on varying criteria.

CellRange: The cell range is the data parameter for the calculation to be implemented on or for. In our example this will be B4:B10. A cell range can be individual cells or an entire column. 

<>: This is the not equal to operator, used to define that two values are separate to one another.

In our example our formula is:
=AVERAGEIF(B4:B10,"<>")

Using Average IF in Google Sheets: Formula input

 

3. Press Enter to see the results 

With the formula inputted, simply press the Enter key. The formula will run and the results will populate.

In our example the result of the Average If calculation is 41.6 and is populated in the cell D5.

How do I use Average IF in Google Sheets: Formula running 

TIP: You can also use the AVERAGE function on a range of cells and it will automatically ignore blank cells.

Average IF Not Blank Using the AVERAGEIFS Function

1. Select an empty cell in your spreadsheet 

Click an empty cell in your sheet, this will be the cell the results populate in.

In our example we have highlighted cell D6.

Using Average IFS in Google Sheets: Select a new cell 

2. Enter the formula =AVERAGEIFS(CellRange1, CellRange2, “<>, CellRange3,"Condition")

In the empty cell input the formula in the format of:
=AVERAGEIFS(Cell Range 1, Cell Range 2, “<>, Cell Range 3,"Condition")

Formula Breakdown:

=AVERAGEIFS: Function that returns the average of a range of data depending on varying criteria.

CellRange1: This is the first range of cells to be used by the Average IF calculation.

CellRange2: The second range of cells used by the Average IF calculation.

<>: This is the not equal to operator, used to define that two values are separate to one another

CellRange3:
The third range of data for the calculation which will be the same as the first in our example and will be utilized in the following function. 

Condition: The condition in this formula will be the greater than operator (>) followed by the number that will be the starting point for the calculation, in our example this will be ‘>10’: a value greater than 10.

In our example we want to find the average value of the cell ranges B4:B10 and C4:C10 only factoring the values based on the condition of >10 while ignoring blank cells, therefore our example formula is:
=AVERAGEIFS(B4:B10, C4:C10,"<>",B4:B10,">10")

Average IF used in Google Sheets: Entering the formula

 

 3. Press Enter to populate the results

With the formula inputted, simply press the Enter key. The formula will run and the results will populate. 

The Average of the data comparison has been generated based on the parameters in the formula with the blank cells not factored into the calculation. 

As can be seen in our below example, the formula result was 36.66666667.

How do i use Average Ifs in Google Sheets: Formula results generated

If you enjoyed this article, you might also like our article on how to use the Google Sheets AVERAGE IF function or our article on how to set up Google Sheets AVERAGE IF not zero.  

If you want to learn how to export email addresses from Google Sheets to Gmail, we also suggest checking out our detailed guide. 

Use our tax return due date tracking software to easily set up custom reminders from your spreadsheet in just a few clicks. 

Automate everything you track in spreadsheets with Lido
Learn more

Level up your Google Sheets skills with our free Google Sheets automation guide

Wasting too much time doing things manually in spreadsheets? Want to spend more time doing what you love? Our 100% free, 27-page Google Sheets automation guide is full of new tips and tricks that will save you time and money!