Function Problems

How to Count Drop-Down Items Except N/A in Excel

Rana Garcia Sep 30, 2026 868 views

Question details

The user needs an Excel formula to count specific entries from drop-down lists across multiple columns while actively ignoring the "N/A" text value.

How to Count Drop-Down Items Except N/A in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Calculating the total number of valid user selections or behaviors in a row by counting cells with data from a drop-down menu, while excluding non-applicable entries marked as "N/A".
Observed behavior: The goal is to accurately calculate the total valid selections (e.g., outputting 2, 3, or 1 per row) without the "N/A" string distorting the total count.

Before you start

Verify the exact column range containing your drop-down lists (e.g., columns A through C) and ensure that "N/A" is entered as plain text, not generated as a formula error (#N/A).

Solution 1Recommended

Use the COUNTIF Formula to Exclude N/A

The most efficient way to count cells that do not match a specific text string is by using the COUNTIF function combined with the "not equal to" operator (<>).

The COUNTIF function is designed to count the number of cells within a range that meet a single criterion. By pairing the not-equal-to symbol with your target text, you instruct Excel to count everything else.

Select the output cell

Click on the empty cell at the end of your data row where you want the total count to appear (for example, cell D2).

Enter the COUNTIF formula

Type `=COUNTIF(A2:C2, "<>N/A")` into the formula bar. Replace A2:C2 with the actual range of your drop-down items.

Apply and fill down

Press Enter on your keyboard to generate the count for the first row. Click and hold the small green square (fill handle) at the bottom-right corner of the cell, then drag it down to copy the formula to the remaining rows.

Formula Tip: The operator <> translates to 'not equal to' in Excel. Surrounding it with quotation marks alongside your text ensures Excel reads it as a valid logical condition.

Solution 2

Use COUNTIFS to Exclude N/A and Blank Cells

If your columns contain empty cells that you also want to prevent from being counted, upgrading to the COUNTIFS function allows for multiple conditions.

Easy Spreadsheet Formulas

Count and Analyze Data Seamlessly in WPS Spreadsheet

WPS Office provides a powerful, free Spreadsheet tool that fully supports standard Excel formulas, including COUNTIF and COUNTIFS. You can quickly analyze drop-down data, exclude specific values, and manage large datasets with an intuitive and familiar interface.

1. Open your spreadsheet: Launch WPS Office and open your workbook containing the drop-down lists.
2. Input the COUNTIF formula: Click your designated total cell and enter `=COUNTIF(A2:C2, "<>N/A")`.
3. Drag the fill handle: Hover over the bottom right of the formula cell and drag it down to calculate the data for all rows instantly.

Fully compatible with Microsoft Excel (.xlsx, .xls) file formats and formulas.Lightweight software that processes complex data counting seamlessly.Free to use with a familiar, user-friendly interface.Cross-platform support allowing you to edit spreadsheets on Windows, Mac, and mobile.

Frequently Asked Questions

Why is my COUNTIF formula returning an error or incorrect count?

Ensure that the text string "N/A" is properly enclosed in quotation marks alongside the not-equal operator, exactly formatted as "<>N/A". Additionally, check if there are hidden trailing spaces in your drop-down list items that might prevent a match.

Can I exclude multiple different text values at once?

The standard COUNTIF function only handles a single condition. To exclude multiple different values (for example, excluding both "N/A" and "Pending"), you must use the COUNTIFS function instead, setting up a criterion for each word.

How do I handle actual #N/A formula errors instead of plain text?

If your cells contain the #N/A error generated by another lookup formula rather than plain text, you cannot use "<>N/A". Instead, you must use an array formula like `=SUM(--NOT(ISNA(A2:C2)))` or utilize the AGGREGATE function to ignore errors during your calculation.

Excel & Data Problems