Fix COUNTIF and COUNTIFS Percentage Formulas in Excel
Question details
Calculate the percentage of specific requirements (such as 'Yes' or 'Waiver') in an Excel Table while avoiding argument and spill errors.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- The user is trying to calculate a completion or acceptance percentage based on multiple text conditions within a single row of an Excel Table.
- Observed behavior
- Using standard dynamic array formulas inside an Excel Table results in spill errors or incorrect percentage calculations because arrays cannot spill inside table cells.
Ensure that your target data range does not contain hidden trailing spaces, and that you understand the exact column headers you are referencing in your table row.
Use SUM and COUNTIFS with an Array Constant
Combine the SUM and COUNTIFS functions with an inline array to count multiple specific text values, then divide by the total populated cells to get a percentage.
By wrapping a COUNTIFS function inside a SUM function, you can count multiple criteria at once using an array constant (e.g., {"Yes","Waiver"}). Dividing this by COUNTA gives the percentage of those specific values against all non-empty cells in the range.
Determine the specific named range or cell range (for example, 'req') that contains the responses you want to evaluate.
Select the target cell and enter the formula: =IFERROR(SUM(COUNTIFS(req,{"Yes","Waiver"}))/COUNTA(req),"")
Press Enter. The formula returns a decimal number. Select the cell, go to the Home tab, and click the Percentage (%) icon to format it correctly.

Use the LET Function within Excel Tables
Use the LET function to define the table row range as a variable, preventing dynamic array spill errors that occur inside structured Excel Tables.
Calculate Multi-Criteria Percentages with WPS Spreadsheet
WPS Spreadsheet provides comprehensive support for advanced array functions, structured table references, and the LET function. You can easily calculate complex multi-criteria percentages without worrying about formula compatibility.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet document.
- 2. Select the output cell: Click on the cell at the end of your data row where you wish to display the calculated percentage.
- 3. Input the formula: Enter your combined percentage formula, such as =IFERROR(SUM(COUNTIFS(req,{"Yes","Waiver"}))/COUNTA(req),"").
- 4. Apply the calculation: Press the Enter key. The software will process the array constant and output a decimal value.
- 5. Format the result: Navigate to the Home tab and click the '%' icon to instantly convert the decimal to a readable percentage format.

Frequently Asked Questions
Why does my COUNTIFS formula return a #SPILL! error in an Excel Table?
Excel Tables do not support dynamic array formulas that attempt to spill outputs into neighboring cells. When passing multiple criteria into COUNTIFS inside a table, you must wrap it in an aggregation function like SUM to condense the array into a single result.
Can I count more than two text criteria using this array method?
Yes, you can add as many conditions as needed to the array constant. For example, using {"Yes","Waiver","Pending"} will prompt the formula to count all three values before the SUM function adds them together.
How do I prevent the formula from calculating empty rows?
Wrapping your main calculation in the IFERROR function (e.g., =IFERROR(YourFormula, "")) ensures that if COUNTA detects an empty row and attempts to divide by zero, the cell will simply display as blank instead of showing a #DIV/0! error.




