How to Use COUNTIFS and MINIFS to Mark the Correct Course Result as Up to Date in WPS Spreadsheets
Question details
The user needs a formula that marks the correct course record as Up to Date and other records as Ignore when a course can appear multiple times with different results and ranking values.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A course can occur once or multiple times in a dataset, and each record has a result status and a ranking value. The user wants to automatically identify the appropriate current record.
- Observed behavior
- The formula should return Up to Date for the completed record with the lowest ranking value for each course, and Ignore for all other records.
Prepare your worksheet so that each row has a consistent course identifier column, a result column, and a numeric ranking column. Confirm the exact completed-result text, such as Completed, so the formula criteria match your data.
Use COUNTIFS and MINIFS to Mark the Correct Course Record as Up to Date
Use this formula approach when a course or ID appears multiple times and you need to mark only the completed record with the lowest ranking as current.
COUNTIFS first checks whether a course has at least one completed result. MINIFS then finds the lowest ranking among completed records for that course. The IF and AND conditions mark the matching completed record as Up to Date, and every other row becomes Ignore.
In WPS Spreadsheets, open your course workbook and click the first empty cell in a helper column, such as D2.
Type =IF(COUNTIFS($A:$A,$A2,$B:$B,"Completed")=0,"Ignore",IF(AND($B2="Completed",$C2=MINIFS($C:$C,$A:$A,$A2,$B:$B,"Completed")),"Up to Date","Ignore")) and adjust A, B, and C to match your Course ID, Result, and Ranking columns.
Press Enter to apply the formula to the first row, then hover over the lower-right corner of the cell and double-click the fill handle to copy it through the remaining course records.
Check that only the intended record per course shows Up to Date. If you add more rows, expand the column ranges or convert the data to a WPS Spreadsheets Table so the ranges update automatically.
Apply the Course Status Formula in WPS Spreadsheets
Open your existing Excel workbook in WPS Spreadsheets, use COUNTIFS and MINIFS to mark the current course record as Up to Date, and save the file back in its original Excel-compatible format without changing your workflow.
- 1. Download WPS Office Free: Go to the official WPS website and click Download WPS Office Free, then install the suite on your device.
- 2. Open the course workbook: Launch WPS Spreadsheets and open the .xlsx or .xls file that contains your course result records.
- 3. Apply the formula: Select the first helper cell, enter the IF, AND, COUNTIFS, and MINIFS formula, and adjust the column references to your data.
- 4. Fill and save: Press Enter, fill the formula down through all records, confirm the Up to Date and Ignore results, and save the workbook.
Frequently Asked Questions
Why does the formula use both COUNTIFS and MINIFS?
COUNTIFS checks whether a course has at least one completed result, while MINIFS returns the lowest ranking among that course's completed records. The IF and AND functions then mark only the matching record as Up to Date.
What if my completed-result text is not exactly Completed?
Replace Completed in the formula with your exact result value, such as Passed or Done. The text must be identical to the value used in the Result column, including capitalization.
Can I use this formula in older versions of Excel?
MINIFS is available in Excel 2016 and later and in WPS Spreadsheets. If you are using an older Excel version, use an array formula with MIN and IF instead.
How do I handle two records with the same lowest ranking?
The basic formula marks both matching rows as Up to Date. To select only one, add a tie-breaker column such as record date or record ID and include it in the MINIFS comparison.
Can I apply this to multiple courses without editing the formula for each group?
Yes. The full-column ranges evaluate all courses at once, and the relative row references adjust automatically as you fill the formula down. Expand the ranges if your data grows beyond the current rows.
