AVERAGEIFS in WPS Spreadsheet: Average Values Meeting Multiple Conditions
Use WPS AVERAGEIFS with aligned criteria ranges, inclusive and exclusive boundaries, wildcard controls and correctly pooled group means.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: AVERAGEIFS returns the arithmetic mean of values whose corresponding cells satisfy every supplied condition. The average range comes first. For a region and a product condition, a row must satisfy both; supplying two criteria does not create an OR selection.
Availability and version history
- Introduced: documented by 2019-06-10 in the official WPS lesson; original introduction is not established.
- Availability: WPS Office for Windows Personal; no numeric release is stated in the edition metadata.
- Scope: exact current supported builds remain unverified. The Windows lesson does not establish equivalent availability in other WPS clients.
- Checked: October 14, 2026 against function-specific documentation. Examples were independently calculated; no native WPS test was performed.
Syntax and aligned ranges
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
The average range and first criterion pair are required; further pairs are optional. The 2019 Windows Personal lesson describes up to 127 criteria. This is dated documentation, not a verified maximum for every current build.
Use ranges with the same shape and corresponding positions. The lesson’s shape note mistakenly calls the comparison range “sum_range,” although this function has no such argument; its definition and walkthrough pair equally shaped criteria ranges with the average range. The examples follow that aligned shape and do not certify the exact error from a malformed shape.
Examples use English names and comma separators. Square brackets mark optional arguments and are not typed. Enter the following data in A1:D9; Row is explanatory, not an extra worksheet column.
| Row | A · Team | B · Plan | C · Score | D · Days |
|---|---|---|---|---|
| 1 | Team | Plan | Score | Days |
| 2 | Red | Core | 84 | 2 |
| 3 | Blue | Core | 60 | 3 |
| 4 | Red | Plus | 96 | 5 |
| 5 | Red | Core | 72 | 7 |
| 6 | Blue | Plus | 88 | 4 |
| 7 | Red | Plus | 90 | 1 |
| 8 | Green | Core | 0 | 6 |
| 9 | Green | Core | 70 | 8 |
Keep outputs outside the table and store Score and Days as numbers.
Example 1: combine two text conditions
=AVERAGEIFS(C2:C9,A2:A9,"Red",B2:B9,"Plus")
Expected result: 93. Rows 4 and 7 supply scores 96 and 90, so the mean is (96+90)/2. The Red/Core rows and the Blue/Plus row fail one of the conditions and are excluded.
For interactive criteria, put Red in F2 and Plus in F3:
=AVERAGEIFS($C$2:$C$9,$A$2:$A$9,F2,$B$2:$B$9,F3)
It also gives 93. Changing F3 to Core produces 78 from 84 and 72. Absolute range references keep the data fixed if you copy the formula.
Example 2: apply a condition to the values being averaged
=AVERAGEIFS(C2:C9,A2:A9,"Red",C2:C9,">=80")
Expected result: 90, from scores 84, 96 and 90. A range can supply both the values and one of the tests, provided the positions remain aligned. The score 72 is excluded even though its team matches.
If a minimum score is stored in F4 as the number 80, replace the last criterion with ">="&F4. The comparison operator belongs in quotes; the reference is concatenated outside them.
Advanced example: use a half-open interval
=AVERAGEIFS(C2:C9,D2:D9,">=2",D2:D9,"<6")
Expected result: 82, from 84, 60, 96 and 88. Day 2 is included; day 6 is excluded. Changing the last test to "<=6" includes the zero score on day 6, giving 65.6 from five observations.
A zero is a numeric observation, not a missing row. The changed denominator matters as much as the unchanged sum of 328. Explicit bounds also make adjacent reporting intervals easier to combine without accidentally including a boundary twice.
Advanced example: combine groups without averaging averages
The Red group has scores totalling 342 across four observations, with mean 85.5. Blue has 148 across two, with mean 74. Their unweighted mean is 79.75, but the six-observation pooled mean is approximately 81.666666667:
=(AVERAGEIFS(C2:C9,A2:A9,"Red")*4+AVERAGEIFS(C2:C9,A2:A9,"Blue")*2)/6
The counts are explicit properties of this complete numeric fixture. In real data, determine the actual numeric-observation denominators separately and check empty or invalid selected values. The formula is not a general missing-data or error-handling recipe.
For counts under multiple conditions, see COUNTIFS. Its count of matched positions is not automatically the denominator of every mixed-type average.
Advanced controls: blank criteria cells and literal wildcards
In K2:K5 enter 0, 1, 0 and a genuinely empty cell. In L2:L5 enter 10, 20, 30 and 40:
=AVERAGEIFS(L2:L5,K2:K5,0)
The lesson treats empty cells within a criteria range as zero. The expected result is approximately 26.666666667, selecting 10, 30 and 40. This control concerns empty cells being tested, not every possible empty criterion argument.
On another sheet, enter codes A*B, AxB, A?B and AB in A2:A5 with values 10, 20, 30 and 40 in B2:B5:
=AVERAGEIFS(B2:B5,A2:A5,"A?B")
=AVERAGEIFS(B2:B5,A2:A5,"A*B")
=AVERAGEIFS(B2:B5,A2:A5,"A~*B")
=AVERAGEIFS(B2:B5,A2:A5,"A~?B")
Expected results are 20, 25, 10 and 30. The question mark matches one character, the asterisk a sequence, and a tilde escapes the following question mark or asterisk. These finite ASCII examples do not certify all locale-specific matching rules.
No matches and source limitations
=AVERAGEIFS(C2:C9,A2:A9,"Purple") has no matching observations. The WPS lesson documents #DIV/0! for this case; reporting zero would change the meaning from “no observations” to an actual zero mean.
The same lesson has less precise statements about empty/text average ranges, Boolean conversion and an error written “#DIV0!”. Those statements do not establish a complete mixed-type or native-error matrix here. Primary examples deliberately use numeric scores, and no undocumented current-build behavior is inferred from another spreadsheet product.
References and verification
- WPS Academy: AVERAGEIFS lesson, 2019-06-10 (Chinese; Windows Personal scope).
- Editorial testing standards.
Worked outputs are independently calculated expectations, not captured native results. The dated source establishes the stated product scope, not every current client or build.