'%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)
How to Sum the Highest N Numbers in Excel While Ignoring Blank Cells
Question details
The user wants to sum a specific number of the highest numeric values (top N) in a dataset but encounters a #NUM! error when the range contains blank or missing entries.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating a final total based on the best N results (e.g., grading the top 7 out of 9 assignments) when some scores have not yet been entered, leaving cells blank.
- Observed behavior
- Using standard SUM and LARGE functions results in a #NUM! error because the formula requests more numbers than the range currently contains due to the blank cells.
Ensure you know the exact target range of your data and the specific number (N) of top values you wish to sum. Verify that your blank cells are truly empty and do not contain hidden space characters.
Use a Dynamic Array Formula with COUNT and SEQUENCE
This method dynamically limits the LARGE function to request only the available number of numeric values, successfully bypassing the #NUM! error when dealing with incomplete data.
The standard LARGE function throws a #NUM! error if you ask for the Nth largest value but the range contains fewer than N numbers.
By combining IF, COUNT, SUM, LARGE, SEQUENCE, and MIN, the formula dynamically limits the request to the actual count of numbers present. This allows it to smoothly ignore blanks and correctly sum whatever top values are currently available.
Click on the cell where you want the final sum calculation to appear.
Type `=IF(COUNT(range)=0,0,SUM(LARGE(range,SEQUENCE(MIN(n,COUNT(range))))))` into the formula bar. Replace 'range' with your actual data range and 'n' with the number of top values you want to sum.
For example, to sum the best 7 scores in cells A1 through A9, type: `=IF(COUNT(A1:A9)=0,0,SUM(LARGE(A1:A9,SEQUENCE(MIN(7,COUNT(A1:A9))))))` and press Enter.
Calculate Top Values Seamlessly in WPS Spreadsheet
WPS Office Spreadsheet fully supports advanced array formulas, including SEQUENCE, LARGE, and nested logical checks. It handles complex grading structures and data summaries just as powerfully as Microsoft Excel.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the scores or incomplete data entries.
- 2. Apply the dynamic formula: Select the destination cell and input the combined IF, SUM, and LARGE array formula provided in the solution.
- 3. Calculate and update instantly: Hit Enter to view the accurate sum. Any blank cells updated with data later will dynamically refresh your total.
Frequently Asked Questions
Why does the LARGE function return a #NUM! error with blank cells?
The LARGE function returns a #NUM! error when the 'k' parameter (the target position from the largest value) is greater than the total number of data points available in the referenced array. Blanks reduce the total data points, causing the error if the target 'N' isn't adjusted dynamically.
Can I use this formula in older spreadsheet versions?
The SEQUENCE function is available in newer software versions (Microsoft 365, Excel 2021, and modern WPS Office). For older versions, you may need to use an array formula utilizing ROW and INDIRECT instead of SEQUENCE, and execute it using Ctrl+Shift+Enter.
Will this formula ignore cells with text or zero values?
The formula ignores blank cells and text strings because the COUNT function only tallies true numerical values. However, if a cell explicitly contains a zero (0), it is counted as a valid numeric entry and will be evaluated by the LARGE function.
How do I sum the lowest N numbers instead of the highest?
To sum the lowest numbers while ignoring blanks, simply replace the LARGE function in the formula with the SMALL function. All other logic handling the blank cells remains exactly the same.
