How to Sum the Last 90 Games by Player and Date in Excel
Question details
The user needs an Excel formula to dynamically calculate a player's win ratio by summing the wins and total games specifically from their most recent 90 records, filtered by player name and sorted by play date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating sports statistics and handicap percentages based on a rolling window of recent game records.
- Observed behavior
- The goal is to extract exactly the last 90 records for a specific player from a larger dataset and calculate the sum of their wins divided by the sum of those 90 games played.
Ensure that your play dates are formatted as valid Excel dates. If your dates are split across multiple columns for year, month, and day, combine them into a single column using the =DATE() function before proceeding.
Use Dynamic Array Formulas (FILTER, SORT, TAKE)
Combine modern Excel dynamic array functions to easily filter records by player, sort them by date, and extract exactly the latest 90 games for summation.
Modern versions of Excel (Microsoft 365) include dynamic array functions that make extracting rolling subsets of data highly efficient.
By nesting FILTER, SORT, and TAKE, you can build a single, robust formula that automatically adjusts if you add new game records to the bottom of your dataset.
In your summary table, create a list of all players by typing =UNIQUE(PlayerRange) into a blank cell. This will spill out all individual player names from your main dataset.
Next to a player's name, type a formula to get the sum of their last 90 wins. Assuming A is the player's name in the summary table, use: =SUM(TAKE(SORT(FILTER(Play[Wins], Play[Player]=A2), Play[Date], -1), 90)). This filters for the specific player, sorts dates descending, takes the top 90 rows, and sums the result.
To get the percentage, divide the previous sum by the total games played in that same 90-game window. Complete the formula: =SUM(TAKE(SORT(FILTER(Play[Wins], Play[Player]=A2), Play[Date], -1), 90)) / SUM(TAKE(SORT(FILTER(Play[Total], Play[Player]=A2), Play[Date], -1), 90)).
Use Helper Columns and SUMIFS (For Older Excel Versions)
If you are using a version of Excel that lacks dynamic array functions, use a ranked helper column to isolate the most recent 90 records for each player.
Calculate Complex Sports Statistics with WPS Spreadsheet
WPS Office provides powerful and highly compatible spreadsheet features, including support for complex dynamic array formulas. You can easily calculate rolling averages, track game statistics, and filter massive datasets seamlessly.
- 1. Open your sports dataset: Launch WPS Spreadsheet and open your existing .xlsx workbook containing the game records.
- 2. Ensure unified date formatting: Verify that your play dates are in a standard date format. If split into separate columns, use the =DATE() function in a new column.
- 3. Apply dynamic array formulas: Create a summary sheet and use a combination of FILTER, SORT, and TAKE functions exactly as you would in modern Excel to calculate the last 90 records dynamically.
Frequently Asked Questions
What happens if a player has fewer than 90 games recorded?
If a player has less than 90 games, the TAKE function will simply return all available records for that player without returning an error. Your calculation will accurately reflect their percentage based on the total games they have played so far.
Why does the FILTER function result in a #CALC! error?
A #CALC! error typically means the FILTER function could not find any records that match your criteria. Verify that the player's name in your summary table perfectly matches the name in your source data, ensuring there are no hidden trailing spaces.
Should I use Excel Tables for this formula?
Yes, it is highly recommended to format your source dataset as an Excel Table (Ctrl+T). This allows you to use structured references like Play[Wins] or Play[Date] in your formulas, which automatically expand to include new data when future games are logged.
