Count Unique Home and Away Game Dates in WPS Spreadsheets
Question details
Count distinct match dates for home and away games when multiple worksheet rows can belong to the same match.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The worksheet stores match dates in column C and uses column F to identify away games with @ and home games with blank cells.
- Observed behavior
- A standard row count can count several records for the same match date, while the required result is one count per unique date.
Confirm that column C contains real spreadsheet dates rather than text values, and use ranges that begin below the header row. Make sure the @ symbol and blank cells in column F are used consistently for game status.
Count unique away or home game dates with FILTER and UNIQUE
Use this method when several rows can represent one game and you need each qualifying date counted only once.
FILTER returns only dates that match the selected game status, and UNIQUE removes duplicate dates before COUNTA counts the remaining results. Change the row limits to match your data range.
Select an empty result cell and enter =COUNTA(UNIQUE(FILTER(C3:C500,F3:F500="@"))). Press Enter to count each distinct date in column C where column F contains @.
Select another empty result cell and enter =COUNTA(UNIQUE(FILTER(C3:C500,F3:F500=""))). Press Enter to count each distinct date where the corresponding cell in column F is blank.
If a status may have no matching rows, use =IFERROR(ROWS(UNIQUE(FILTER(C3:C500,F3:F500="@"))),0) for away games, replacing @ with "" for home games. This returns 0 instead of an error.
Use COUNTIFS when every row is a separate game
Use COUNTIFS when each row already represents one game and you need to apply several criteria without removing duplicate dates.
Count Distinct Game Dates with WPS Spreadsheets
WPS Spreadsheets lets you combine FILTER, UNIQUE, COUNTA, and ROWS to summarize home and away schedules without counting repeated records for the same game date.
- 1. Open the schedule workbook: Open your game schedule in WPS Spreadsheets and verify that the date column and home/away status column are populated.
- 2. Choose a summary cell: Click an empty cell where you want the away-game or home-game total to appear.
- 3. Enter the distinct-date formula: Type =COUNTA(UNIQUE(FILTER(C3:C500,F3:F500="@"))) to count unique away dates, then press Enter.
- 4. Create the home-game total: In another empty cell, replace the @ criterion with "" to count unique dates whose status cell is blank.
Frequently Asked Questions
Why does COUNTIFS give a larger number than the number of games played?
COUNTIFS counts matching rows. If one game has multiple rows with the same date, every matching row is included. Use UNIQUE(FILTER(...)) to count each date once.
How do I count away games marked with an @ symbol?
Use =COUNTA(UNIQUE(FILTER(C3:C500,F3:F500="@"))). Column C supplies the dates, and column F supplies the away-game marker.
How can I count unique home-game dates when the status cell is blank?
Use =COUNTA(UNIQUE(FILTER(C3:C500,F3:F500=""))). The empty-string criterion selects blank cells in column F.
What formula returns 0 when there are no away games?
Use =IFERROR(ROWS(UNIQUE(FILTER(C3:C500,F3:F500="@"))),0). IFERROR converts the no-match calculation error to 0.
Can I use these formulas with an Excel workbook in WPS Spreadsheets?
Yes. WPS Spreadsheets can open and edit Excel .xlsx and .xls files, allowing you to add these formulas to compatible workbooks.
