Function Problems

How to Exclude Specific Values from a COUNTA Formula in Excel

Adam Davis Oct 1, 2026 869 views

Question details

The user needs to count departments in a structured table row but wants to exclude specific function names from the final calculation.

How to Exclude Specific Values from an Excel COUNTA Formula

Product: Microsoft Excel
Device & OS: not provided
Scenario: Counting specific non-blank cells within an Excel table range (e.g., Table2[@[PICK]:[SD]]) while intentionally ignoring predefined text strings.
Observed behavior: The standard COUNTA formula counts all non-empty cells in the specified range, meaning the function names are incorrectly included in the department total.

Before you start

Identify the exact text strings or criteria you want to exclude, and verify that your structured table references are correctly defined in your worksheet.

Solution 1Recommended

Subtract Excluded Values Using COUNTIF

The most straightforward method is to count all non-blank cells using COUNTA and then subtract the count of the specific text values you want to ignore using COUNTIF.

This method is highly effective if you only have one or two specific text strings (like a single function name) that you need to exclude from your department count.

Select the target cell

Click on the cell where you want the final department count to appear.

Enter the combined formula

Type the formula: =COUNTA(Table2[@[PICK]:[SD]]) - COUNTIF(Table2[@[PICK]:[SD]], "Function Name"). Replace "Function Name" with the exact text you wish to exclude.

Apply the calculation

Press Enter to apply the formula. If your data is in a table format, the formula should automatically fill down the column.

Formula Tip: You can subtract multiple COUNTIF functions if you need to exclude a few different specific names.

Solution 2

Use COUNTIFS for Criteria-Based Counting

Instead of counting everything and subtracting, you can use the COUNTIFS function to directly count only the cells that meet your specific criteria.

Solution 3

Use SUMPRODUCT to Exclude Multiple Different Values

If you have a complex scenario where you need to exclude a list of multiple different function names, SUMPRODUCT provides a robust array-based solution.

Efficiently Manage and Calculate Data with WPS Spreadsheet

WPS Office provides a powerful spreadsheet tool that fully supports complex array formulas, structured table references, and functions like COUNTA, COUNTIF, and SUMPRODUCT to help you analyze your data without limits.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the department table.
2. Select the calculation cell: Click the specific cell where you want the filtered count to appear.
3. Enter the exclusion formula: Type your combined COUNTA and COUNTIF formula to exclude the specific function text.
4. Apply across rows: Press Enter, and use the fill handle to seamlessly apply the formula across your entire table.

100% compatible with Microsoft Excel (.xlsx) formulas and structured table references.Lightweight and fast, easily handling large datasets and complex array formulas.Built-in formula auditing and error-checking tools to help troubleshoot calculations.Completely free to use with an intuitive, familiar tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does COUNTA include cells that appear empty?

COUNTA counts any cell that is not completely blank. If a cell contains a formula returning an empty string ("") or contains hidden spaces, COUNTA will still count it. You can use COUNTIF with the criteria "?*" to strictly count cells containing visible text.

How do I exclude a dynamic list of words from my count?

You can list the words you want to exclude in a separate range (e.g., Z1:Z5). Then, use an array formula like =SUMPRODUCT(--ISNA(MATCH(Table2[@[PICK]:[SD]], Z1:Z5, 0))) alongside a check for non-blank cells to dynamically ignore those values.

Can I use the FILTER function to count while excluding specific text?

Yes, if you are using modern spreadsheet software that supports dynamic arrays, you can use the formula =COUNTA(FILTER(range, range<>"ExcludedText")). This dynamically filters out the unwanted values before the COUNTA function calculates the total.

Excel & Data Problems