'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Use Excel COUNTIFS with Wildcards for Multiple Text Strings
Question details
The user needs to count cells in a spreadsheet that contain multiple specific partial text strings using a single formula with AND logic.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Counting cells based on multiple partial text matches within a single range.
- Observed behavior
- The user wants an accurate formula to return the count of cells containing both specified text strings (e.g., 'this' and 'that') anywhere within the text.
Ensure your text data is within a contiguous range and identify the exact partial strings you want to search for. Remember that the asterisk (*) wildcard represents any sequence of characters in formulas.
Use the COUNTIFS Function with Asterisk Wildcards
This method applies an AND logic across the same data range, ensuring that a cell is counted only if it contains all specified text strings.
The COUNTIFS function is designed to evaluate multiple criteria. By supplying the exact same cell range multiple times but with different wildcard criteria, you can force the function to count only the cells that contain all your required substrings.
The asterisk (*) is a powerful wildcard that matches any number of characters before, between, or after your target words. Wrapping your target word in asterisks (e.g., "*word*") ensures the formula finds the word regardless of its position in the cell.
Click on the cell where you want the final count result to appear.
Type =COUNTIFS(A1:A100, "*this*", A1:A100, "*that*") into the formula bar. Replace A1:A100 with your actual data range, and change 'this' and 'that' to your specific search terms.
Press Enter. The cell will now evaluate the range and display the total number of cells that contain both text strings.
Use WPS Spreadsheet to Count Complex Text Matches
WPS Spreadsheet fully supports the COUNTIFS function and wildcard characters, making it easy to analyze complex text data and streamline your workflows efficiently.
- 1. Open your dataset in WPS: Launch WPS Office and open your workbook in the Spreadsheet application.
- 2. Insert the formula: Select a destination cell and input =COUNTIFS(A1:A100, "*word1*", A1:A100, "*word2*").
- 3. Get instant results: Press Enter to instantly count the cells matching your multiple wildcard conditions.
Frequently Asked Questions
Can I use OR logic with COUNTIFS instead of AND logic?
No, the COUNTIFS function inherently uses AND logic for its criteria. To count cells containing 'this' OR 'that', you should add two COUNTIF functions together: =COUNTIF(A1:A100,"*this*") + COUNTIF(A1:A100,"*that*"). Keep in mind you may need to subtract the cells that contain both so they aren't counted twice.
Does the order of the text strings matter in the wildcard search?
In the formula =COUNTIFS(range, "*this*", range, "*that*"), the order of 'this' and 'that' within the actual cell does not matter. The formula will count the cell whether its content is 'this and that' or 'that and this'.
Is there a way to make the wildcard search case-sensitive?
The COUNTIFS function is case-insensitive by default. If you need a case-sensitive count, you would need to use a more advanced array formula combining SUMPRODUCT, ISNUMBER, and FIND, because the FIND function is strictly case-sensitive.
What other wildcards can I use in formulas?
Besides the asterisk (*) which matches any sequence of characters, you can use the question mark (?) to match any single character. You can also use the tilde (~) to escape wildcards if you want to find an actual literal asterisk (~*) or question mark (~?).
