Formula Errors

Fix #NAME? Error When Counting Yes and No Answers by ID in Excel

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs to count 'Yes' or 'No' responses across specific non-adjacent columns (such as B, E, and G) for a specific ID, while ignoring non-applicable questions, but receives a #NAME? error.

Product: Excel
Device & OS: not provided
Scenario: Using a formula to tally specific text responses for individual unique IDs across multiple selected columns in a dataset.
Observed behavior: The formula execution fails and returns a #NAME? error instead of the expected numerical count.

Before you start

Verify that your dataset has clearly defined column headers for IDs and responses, and note the exact column letters you want to include in your count to ensure accurate formula setup.

Solution 1Recommended

Troubleshoot the #NAME? Error Syntax

The #NAME? error indicates that Excel cannot recognize a function name, named range, or text element in your formula. Fixing syntax issues resolves this problem.

Excel relies on exact syntax to process calculations. A #NAME? error when counting text like 'Yes' or 'No' usually means quotation marks are missing or a function name is misspelled.

Select the error cell

Click on the cell displaying the #NAME? error to view its contents in the Formula Bar at the top.

Check function spelling

Ensure that your counting function is spelled correctly (e.g., COUNTIFS, not COUNTIFs or COUTNIFS).

Add quotation marks to text

Verify that the words "Yes" and "No" are enclosed in double quotation marks. Typing Yes without quotes causes Excel to look for a named range called Yes, triggering the error.

Verify list separators

Check that you are using the correct separator between arguments (usually a comma or semicolon, depending on your regional settings).

Apply changes

Press Enter to recalculate the formula and confirm the #NAME? error is resolved.

Named Ranges: If you are using named ranges for your ID column or response ranges, open the Name Manager (Ctrl + F3) to ensure those names exist and are spelled exactly as they appear in your formula.

Solution 2

Construct a Formula for Non-Adjacent Columns

Once the syntax errors are fixed, use multiple COUNTIFS functions added together to correctly count responses across separated columns.

Solve it effortlessly with WPS

Avoid Formula Errors Automatically in WPS Spreadsheet

WPS Spreadsheet offers intelligent formula auto-completion and syntax highlighting, making it incredibly easy to count complex data by ID across multiple columns without triggering frustrating #NAME? errors.

1. Open your workbook: Launch WPS Spreadsheet and open your data file containing the IDs and response columns.
2. Use Insert Function: Click the cell for your result, navigate to the Formula tab on the top ribbon, and select Insert Function.
3. Select COUNTIFS: Search for COUNTIFS and let the dialog box guide you to input your ranges and criteria, automatically adding the required quotation marks.
4. Get instant results: Click OK to instantly apply the error-free formula and get your accurate 'Yes' and 'No' counts.

Intelligent formula auto-completion prevents typos and missing quotation marks.Fully compatible with Microsoft Excel formulas, including COUNTIFS and SUMPRODUCT.Built-in error checking instantly highlights the exact cause of formula failures.Lightweight software that runs smoothly even with massive datasets.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show #NAME? when I type Yes or No in my formula?

Excel requires all specific text strings used as criteria in formulas to be enclosed in double quotation marks (e.g., "Yes"). If you omit them, Excel assumes you are referencing a defined name or function that does not exist, resulting in a #NAME? error.

Can I use an array formula to count non-adjacent columns?

Yes. Instead of multiple COUNTIFS, you can use a formula like =SUMPRODUCT((A2:A100="ID_Value")*((B2:B100="Yes")+(E2:E100="Yes")+(G2:G100="Yes"))). This evaluates multiple non-adjacent ranges simultaneously without needing array entry shortcuts in newer Excel versions.

What happens to blank or 'N/A' responses in my COUNTIFS formula?

A COUNTIFS formula specifically looking for "Yes" or "No" will automatically ignore blank cells, "N/A" responses, and any other text that doesn't perfectly match your specified criteria.

How do I fix the #VALUE! error when adding multiple COUNTIFS?

A #VALUE! error often occurs if the ranges in your COUNTIFS function are not the exact same size. Ensure that your ID column range (e.g., A2:A50) perfectly matches the row numbers of your response column ranges (e.g., B2:B50).

Excel & Data Problems