How to Count Rows Where One Column Contains Text and Another Does Not in Excel
Question details
The user needs to count specific rows based on multiple text criteria across two columns, specifically including one text value while excluding another.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering data records to count the exact number of occurrences that meet both an inclusion and an exclusion text criteria.
- Observed behavior
- A formula is needed to output the total count of rows matching the specified text inclusion in Column A and text exclusion in Column B.
Ensure your data ranges are consistent in size and do not contain hidden rows or trailing spaces that might interfere with text recognition in your formulas.
Use COUNTIFS with Wildcards for Partial Text Matches
Best for when you need to find rows where a column contains a specific word within other text, while another column does not contain a specific word.
The COUNTIFS function allows you to apply multiple criteria to different ranges. By combining it with asterisk wildcards (*), you can search for substrings of text anywhere within a cell.
Click on the empty cell where you want the final count result to appear.
Type the formula =COUNTIFS(A:A,"*TEXT 1*",B:B,"<>*ANSWER 1*"). Replace 'TEXT 1' with the word you want to include, and 'ANSWER 1' with the word you want to exclude.
Press Enter to execute the formula and view the total number of matching rows.
Use COUNTIFS for an Exact Match in the First Column
Ideal when the first column must match a specific text exactly, rather than just containing it as part of a larger string.
Count Complex Data Sets Easily with WPS Spreadsheet
WPS Spreadsheet offers full support for advanced Excel functions like COUNTIFS, allowing you to filter, analyze, and manage complex datasets with ease.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing your data set.
- 2. Select output cell: Click on the cell where you wish to display the counted result.
- 3. Type the formula: Enter your =COUNTIFS formula using the specific inclusion and exclusion criteria.
- 4. Execute: Hit Enter to instantly view your calculated row count.
Frequently Asked Questions
Why is my COUNTIFS formula returning 0 even though there are matching rows?
This usually happens if there are invisible trailing spaces in your cells or if the text criteria are misspelled. Try using the TRIM function on your data ranges to remove extra spaces, or verify the spelling of your criteria.
Can I apply this formula to more than two columns?
Yes, the COUNTIFS function can handle up to 127 range/criteria pairs. Just append the next range and criteria separated by commas, such as =COUNTIFS(A:A,"*Text*", B:B,"<>*Text2*", C:C,"Criteria3").
Are COUNTIFS text criteria case-sensitive?
No, the COUNTIFS function in both Microsoft Excel and WPS Spreadsheet is not case-sensitive. Words like "TEXT", "Text", and "text" will be treated as the exact same value.
How do I exclude blank cells from the row count?
To exclude blank cells from your final count, add an additional criteria pair to your COUNTIFS formula: A:A, "<>" which ensures the cell being evaluated is not completely empty.
