'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Excel Formula to Count Players but Exclude "No Score"
Question details
The user needs to count the number of players or text entries in a cell range while ignoring specific cells that contain the phrase "No Score".
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tabulating player scores or participation tallies where incomplete or missing data marked as "No Score" needs to be omitted from the final head count.
- Observed behavior
- Standard functions like COUNTA or COUNTIF may count all populated cells, incorrectly inflating the total by including cells marked as "No Score".
Identify the exact cell range containing your player data and ensure that the text phrase you want to exclude is spelled consistently across all cells.
Use an Array Formula with SUM, IF, and ISTEXT
Combine SUM, IF, and ISTEXT into an array formula to count all valid text entries and subtract those containing the specific phrase.
This formula works by first counting every cell that contains text, and then subtracting the count of cells that end with the exact 8-character phrase "No Score".
Click on an empty cell where you want the final player count to appear.
Type the formula: =SUM(IF(ISTEXT(V2:V10),1))-SUM(IF(RIGHT(V2:V10,8)="No Score",1)). Replace V2:V10 with your actual data range.
In older versions of Excel, you must confirm the formula by pressing Ctrl + Shift + Enter instead of just Enter. In newer versions with dynamic arrays, simply press Enter.
Use the COUNTIFS Function
A modern and simpler alternative using the COUNTIFS function with wildcard operators to exclude specific text.
Easily Count and Manage Data with WPS Spreadsheet
WPS Spreadsheet offers comprehensive support for advanced array formulas and modern functions like COUNTIFS, making it effortless to manage complex data calculations while retaining complete compatibility with your existing Excel files.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your existing Excel workbook containing the player scores.
- 2. Enter your formula: Select the desired cell and type your array or COUNTIFS formula directly into the formula bar.
- 3. Calculate instantly: Press Enter to calculate your filtered count, utilizing WPS's seamless support for both modern and legacy spreadsheet functions.
Frequently Asked Questions
Why does the COUNTA function include cells with "No Score"?
The COUNTA function counts every cell that is not completely blank. Because "No Score" is a valid text string, COUNTA recognizes the cell as populated and includes it in the total.
Can I exclude multiple different phrases using these formulas?
Yes. You can expand the COUNTIFS formula by adding more range and criteria pairs. For example: =COUNTIFS(V2:V10, "?*", V2:V10, "<>*No Score*", V2:V10, "<>*Disqualified*").
What does the RIGHT function do in the array formula?
The RIGHT function extracts a specified number of characters from the end of a text string. In the formula RIGHT(V2:V10,8)="No Score", it checks if the last 8 characters of the cell exactly match the phrase "No Score".
Do I always need to press Ctrl+Shift+Enter for array formulas?
Not always. In modern spreadsheet software with dynamic arrays (like Microsoft 365 or the latest WPS Spreadsheet), you only need to press Enter. Older versions still require Ctrl+Shift+Enter to evaluate the array properly.
