DMAX in WPS Spreadsheet: Find the Maximum Within Matching Records
Use DMAX in WPS Spreadsheet with a complete database and criteria grid, field-name and position examples, and documentation-based limitations.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: DMAX returns the maximum selected numeric field value among matching database records. With the complete worksheet below, =DMAX(A1:D7,"Value",F1:F2) has independent expected result 18.
Syntax and the three required arguments
=DMAX(database,field,criteria)
- database: a rectangular list including its header row. Each following row is a record; each column is a field.
- field: the header text in quotes, or the field’s one-based position within the database. In A1:D7, Value is field 3 and Note is field 4. These positions are relative to the database, not an arbitrary worksheet column number.
- criteria: a separate range containing at least one field header and a condition directly below it. Pass the range including its header, rather than the condition cell alone.
The official lesson permits both field names and positions. Names are easier to read; positional formulas must be revisited when the database’s column order changes. Keep header spelling consistent between the database and the criteria block.
Complete worksheet example
Create a fresh worksheet and enter this database in A1:D7. Leave D3 genuinely empty; the word “blank” in this table is an instruction, not text to enter. Enter the zero in D7 as a number.
| Row | A: ID | B: Group | C: Value | D: Note |
|---|---|---|---|---|
| 1 | ID | Group | Value | Note |
| 2 | 101 | 1 | 12 | Ready |
| 3 | 102 | 1 | 18 | (leave empty) |
| 4 | 103 | 2 | 7 | Review |
| 5 | 104 | 1 | −3 | Hold |
| 6 | 105 | 2 | 0 | Ready |
| 7 | 106 | 1 | 6 | 0 |
Put Group in F1 and numeric 1 in F2. Keep this criteria block outside the database. Enter =DMAX(A1:D7,"Value",F1:F2) in H2.
Group 1 selects 12, 18, −3 and 6. The largest is 18. Group 2 selects 7 and 0, so its maximum is 7. The −3 is a genuine numeric observation, not a blank or an error.
To check positional selection, replace "Value" with 3. The expected result for the same criterion is unchanged. Use absolute references such as $A$1:$D$7 when copying a formula should preserve the database range, and check whether the criteria reference should remain fixed or move to another block.
Advanced use: compare group-specific upper values
Build a short summary with one criteria block for Group 1 and a separate block for Group 2. The expected maxima are 18 and 7, respectively, so their difference is 11. This comparison uses the same Value field and the same measurement units in both groups.
DMAX returns the maximum value rather than the ID or entire record containing it. If multiple matching records share the maximum, the numerical maximum still does not tell you which record to report. Do not convert this into a unique-record lookup without checking ties separately.
To isolate one known record, use ID/104 criteria. The expected maximum is −3, because the selection contains only that one number. This control illustrates why a result of zero should not automatically be substituted for a negative maximum.
Errors, empty selections and criteria limits
The cited lesson does not specify a complete error table or the result for a selection with no matching records. This guide therefore does not assign an empty-selection default or an invented error code. Before using such an outcome in a production rule, verify it in the intended WPS edition.
The worked Value field contains only numbers. Text, logical values, formula-empty strings and errors mixed into that field are outside the examples; no coercion or error-propagation rule is inferred for them.
Numeric equality criteria are used deliberately. Wildcards, text prefix matching, case sensitivity, multiple criteria rows, blank criteria and calculated-condition formulas need their own product-specific evidence. Do not generalize these small examples into an unrestricted filtering-language specification. Keep the criteria range exact: accidentally including additional rows changes the expression you are asking WPS to evaluate.
Evidence, availability and compatibility
The official WPS Academy DMAX lesson was published 2019-05-31 and reviewed on October 18, 2026. It explicitly identifies WPS Office for Windows Personal, with no numeric build. The publication date is evidence of documentation by that date, not the first release. Introduction and last-supported versions remain unknown. The source does not identify this function as deprecated or retired.
These are original documentation-based examples with independent record selection and arithmetic. They were not executed in WPS. No screenshot or cross-edition execution is claimed. Check separators, localized entry and workbook interchange in the intended client before relying on them; file-format round trips were not tested.