Return a Blank Result When an Excel AVERAGEIF Criteria Cell Is Empty
Question details
The user wants an AVERAGEIF formula to show a blank result when its 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.
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.
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.
In Excel, select a cell outside AO2 and the average range where you want the result to appear.
In the formula bar, enter =IF(AO2="","",AVERAGEIF($AO$2:$AO$5344,AO2,$K$2:$K$5344)) and press Enter.
Leave AO2 empty to see a blank display, then enter a criterion in AO2 to calculate the average of matching values in K2:K5344.

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. Open the workbook: In WPS Spreadsheets, choose Open and select your Excel workbook.
- 2. Select a result cell: Click a cell outside AO2 and K2:K5344 where you want to display the average.
- 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. Test the criterion: Leave AO2 empty to check the blank display, then type a criterion into AO2 to calculate the matching average.

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.




