Function Problems

Return a Blank Result When an Excel AVERAGEIF Criteria Cell Is Empty

Anum MushtaqAnum Mushtaq Nov 18, 2026 868 views

Question details

The user wants an AVERAGEIF formula to show a blank result when its criteria cell is empty.

Return a Blank Result When an Excel AVERAGEIF Criteria Cell Is Empty
Product
Microsoft Excel
Device & OS
not provided
Scenario
Calculate the average of values in column K based on a criterion in AO2, but only when AO2 contains a value.
Observed behavior
The desired result is a blank display when AO2 is empty and an average when AO2 contains a criterion.
Before you start

Identify the criteria cell, criteria range, and average range in your worksheet. Enter the formula in a cell other than AO2 or the cells it averages to avoid a circular reference.

Solution 1Recommended

Wrap AVERAGEIF in an IF Test for an Empty Criteria Cell

Use IF to skip the average when AO2 is empty and calculate it otherwise.

1
Select the result cell

In Excel, select a cell outside AO2 and the average range where you want the result to appear.

2
Enter the formula

In the formula bar, enter =IF(AO2="","",AVERAGEIF($AO$2:$AO$5344,AO2,$K$2:$K$5344)) and press Enter.

3
Check the result

Leave AO2 empty to see a blank display, then enter a criterion in AO2 to calculate the average of matching values in K2:K5344.

Wrap AVERAGEIF in an IF Test for an Empty Criteria Cell
Blank display versus empty cell: The formula returns an empty string when AO2 is empty. The result looks blank, but the cell still contains a formula.
WPS Spreadsheets

Show a Blank AVERAGEIF Result in WPS Spreadsheets

Use the same IF and AVERAGEIF formula in WPS Spreadsheets to leave the result looking blank until the criteria cell has a value.

  1. 1. Open the workbook: In WPS Spreadsheets, choose Open and select your Excel workbook.
  2. 2. Select a result cell: Click a cell outside AO2 and K2:K5344 where you want to display the average.
  3. 3. Enter the formula: In the formula bar, enter =IF(AO2="","",AVERAGEIF($AO$2:$AO$5344,AO2,$K$2:$K$5344)) and press Enter.
  4. 4. Test the criterion: Leave AO2 empty to check the blank display, then type a criterion into AO2 to calculate the matching average.
Skip the AVERAGEIF calculation when the criteria cell is emptyOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return an empty string instead of a truly empty cell?

A cell containing a formula is not empty. The "" result makes it appear blank when AO2 is empty.

Can I use ISBLANK instead of AO2=""?

Yes. Use =IF(ISBLANK(AO2),"",AVERAGEIF($AO$2:$AO$5344,AO2,$K$2:$K$5344)) if AO2 is genuinely empty. ISBLANK returns FALSE if AO2 contains a formula, even when that formula displays an empty string.

Why do I see a #DIV/0! error when AO2 has a value?

AVERAGEIF can return #DIV/0! when no matching cells contain numbers to average. Check the criterion in AO2 and the values in K2:K5344.

What do the dollar signs in the ranges do?

The dollar signs keep the criteria and average ranges fixed if you copy the formula to another cell. AO2 is relative, so it can change when the formula is copied.