How to Set Criteria for MINIFS and AVERAGEIF in WPS Office Spreadsheets
Question details
The user needs to understand how to write valid criteria arguments for the MINIFS and AVERAGEIF conditional functions, including text, numeric, wildcard, and cell-reference criteria.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is building formulas that calculate a minimum or average from only the rows or values that meet one or more specified conditions.
- Observed behavior
- Formulas should correctly include only the cells that satisfy the supplied criteria and return the expected minimum or average.
Confirm that your data is arranged in consistent columns, and identify which column is the criteria range and which column contains the values to average or to use for the minimum.
Use MINIFS to Find a Minimum Based on One or Multiple Criteria
Use this when you want the smallest numeric value from a range filtered by one or more conditions, such as a threshold, text label, or partial match.
MINIFS evaluates criteria_range arguments against the criteria and returns the minimum value from min_range for matching rows. In WPS Office Spreadsheets the function uses the same syntax as modern Excel.
Select an empty cell where you want the minimum result, then click in the formula bar to start the formula.
Type =MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). For example, =MINIFS(C2:C100,A2:A100,">=10") returns the smallest value in C2:C100 where A2:A100 is at least 10.
Press Enter to calculate the result, then adjust the criteria arguments if the included rows do not match your expected condition.
Use AVERAGEIF with Text, Wildcard, and Cell-Reference Criteria
Use this when you need the average of values that meet a single condition, including labels, partial matches, or thresholds stored in another cell.
Use MINIFS and AVERAGEIF in WPS Office Spreadsheets
WPS Office Spreadsheets supports MINIFS, AVERAGEIF, and related conditional functions with Excel-compatible syntax, so you can apply numeric, text, wildcard, and cell-reference criteria and save the workbook in .xlsx format.
- 1. Download and install WPS Office: Download WPS Office from the official website, install it, and open WPS Spreadsheets.
- 2. Open or import your workbook: Open an existing .xlsx or .xls file, or enter your data in a new sheet.
- 3. Enter a conditional formula: Click an empty result cell and enter =MINIFS(...) or =AVERAGEIF(...) with the appropriate criteria ranges and values.
- 4. Calculate and save: Press Enter to calculate the result, then save the workbook in .xlsx format to share with Microsoft Excel users.
Frequently Asked Questions
What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF uses a single condition, while AVERAGEIFS supports multiple conditions. In WPS Office Spreadsheets, use AVERAGEIF(range, criteria, [average_range]) and AVERAGEIFS(average_range, criteria_range1, criteria1, ...).
Can MINIFS criteria use a cell reference for a comparison operator?
Yes. Store the threshold in a cell and concatenate the operator in the formula, for example =MINIFS(C2:C100,A2:A100,">="&E2).
Which wildcard characters work in MINIFS and AVERAGEIF criteria?
Use the question mark (?) for any single character and the asterisk (*) for any sequence of characters. To match a literal question mark or asterisk, precede it with a tilde (~).
Why does my MINIFS formula return a #NAME? error?
Check the function name spelling and make sure you are using a WPS Office or Microsoft Excel version that supports MINIFS. Older Excel versions may require a different array formula approach.
Can AVERAGEIF average a different column than the criteria column?
Yes. Provide the optional average_range argument, such as =AVERAGEIF(B2:B100,"Completed",C2:C100), to average C2:C100 based on the condition in B2:B100.
