Function Problems

Fix COUNTIF and COUNTIFS Percentage Formulas in Excel

Phi Hung VoPhi Hung Vo Nov 10, 2026 871 views

Question details

Calculate the percentage of specific requirements (such as 'Yes' or 'Waiver') in an Excel Table while avoiding argument and spill errors.

Fix COUNTIF and COUNTIFS Percentage Formulas in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Define the data range

Determine the specific named range or cell range (for example, 'req') that contains the responses you want to evaluate.

2
Enter the combined formula

Select the target cell and enter the formula: =IFERROR(SUM(COUNTIFS(req,{"Yes","Waiver"}))/COUNTA(req),"")

3
Format as a percentage

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 SUM and COUNTIFS with an Array Constant
Formula Breakdown: The IFERROR wrapper ensures that if the row is completely empty (causing a divide-by-zero error), the cell will remain blank instead of displaying an error.
Free Microsoft Office alternative

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. 1. Open WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet document.
  2. 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. 3. Input the formula: Enter your combined percentage formula, such as =IFERROR(SUM(COUNTIFS(req,{"Yes","Waiver"}))/COUNTA(req),"").
  4. 4. Apply the calculation: Press the Enter key. The software will process the array constant and output a decimal value.
  5. 5. Format the result: Navigate to the Home tab and click the '%' icon to instantly convert the decimal to a readable percentage format.
Fully compatible with Microsoft Excel (.xlsx) files and advanced formulas.Supports dynamic arrays and functions like SUM, COUNTIFS, and IFERROR.Lightweight, fast-loading, and completely free to use for your daily data analysis.
microsoft office alternative - wps office

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.