How to Count COMPLETE or CLOSED Records by Date and Name in WPS Spreadsheets
Question details
The user needs a WPS Spreadsheets formula that counts rows where a status column contains either COMPLETE or CLOSED while also matching a specified date and a specified name.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a worksheet with status, date, and name columns and wants to summarize records that match multiple conditions, including either of two status values.
- Observed behavior
- The goal is to return a single count from a formula that checks all three conditions across the data range.
Confirm that the date column, status column, and name column ranges are the same size and that the criteria cells AB1 and AF2 contain the actual date and name you want to match.
Use SUMPRODUCT with COUNTIFS to handle COMPLETE or CLOSED criteria
Use this when you need one formula to count rows matching a date, a name, and either of two status values without creating helper columns.
The COUNTIFS function normally accepts only one criterion per range. Wrapping COUNTIFS inside SUMPRODUCT lets WPS Spreadsheets evaluate both status criteria in the array constant {"COMPLETE","CLOSED"} and return the total count.
Click the cell where you want the count result to appear in your WPS Spreadsheets workbook.
Type or paste =SUMPRODUCT(COUNTIFS(G2:G21,{"COMPLETE","CLOSED"},A2:A21,AB2,Q2:Q21,AF2)) and adjust the row ranges and criteria cells to match your worksheet.
Press Enter and confirm the returned number matches the records you expect.
Use two COUNTIFS formulas added together for COMPLETE and CLOSED
Use this alternative if you prefer to see each status condition written separately and want to avoid array constants.
Count Records in WPS Spreadsheets with SUMPRODUCT and COUNTIFS
WPS Spreadsheets can directly perform this multi-condition count. Open your workbook, enter the formula, and adjust the ranges to return the total COMPLETE or CLOSED records by date and name.
- 1. Open WPS Spreadsheets: Launch WPS Office and open the workbook that contains the record data.
- 2. Select the result cell: Click the cell where you want the final count to appear.
- 3. Enter the formula: Type =SUMPRODUCT(COUNTIFS(G2:G21,{"COMPLETE","CLOSED"},A2:A21,AB2,Q2:Q21,AF2)) in the formula bar.
- 4. Adjust the ranges and criteria: Update G2:G21, A2:A21, Q2:Q21, AB2, and AF2 to match the actual columns and cells in your worksheet.
- 5. Press Enter and verify: Press Enter and check that the returned value is the expected count.
Frequently Asked Questions
Why does the formula use SUMPRODUCT instead of just COUNTIFS?
COUNTIFS is designed for one condition per range. SUMPRODUCT lets the formula evaluate the array constant {"COMPLETE","CLOSED"} for the status column and sum the counts for both statuses.
Do I need to press Ctrl+Shift+Enter in WPS Spreadsheets?
No. In the recommended formula, SUMPRODUCT naturally handles the array calculation, so a regular Enter is enough.
Can I put COMPLETE and CLOSED in separate cells instead of typing them in the formula?
Yes. You can place COMPLETE and CLOSED in two cells and use two COUNTIFS formulas added together, or adjust the formula to reference a criteria range if your version supports it.
How do I change the formula if my data has more than rows 2 to 21?
Select the formula and update the ranges such as G2:G21, A2:A21, and Q2:Q21 to include all data rows. Keep the ranges the same length and avoid including header rows.
Why does my formula return 0?
Check that the date in AB2 is a real date value, the name in AF2 exactly matches the name column, and the status column contains no extra spaces or lowercase differences.
