Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Countif Wildcards (Asterisks Or Question Marks) in Excel?
Excel is a strong tool that provides a number of tools to assist you in efficiently analysing and manipulating your data. One such useful tool that enables you to count the number of cells that satisfy particular requirements is the COUNTIF function. In this article, we'll concentrate on using the COUNTIF function with wildcards, notably asterisks (*) and question marks (?). When you need to search for values that only loosely match a certain criterion, wildcards—symbols that stand in for unidentified characters or patterns—can be immensely helpful.
You will clearly understand how to use wildcards to count particular patterns or variations of data in your Excel spreadsheets at the end of this session. This article will walk you through the procedure step by step whether you want to count cells that contain a given set of letters or count cells that fit a particular pattern. So let's get started and learn how to effectively count and evaluate your data using Excel's wildcards and the COUNTIF function!
Countif Wildcards (Asterisks Or Question Marks)
Here, we can complete the task directly using the formula. So let us see a simple process to learn how you can count wildcards (asterisks or question marks) in Excel.
Step 1
Consider an Excel sheet where you have question marks and asterisks in the cells, similar to the below image.
First, to count the cells with asterisks, click on an empty cell and enter the formula as =SUM(LEN(A2:A8)-LEN(SUBSTITUTE(A2:A8,"*",""))) and click Ctrl + Shift + Enter. In the formula A2:A10, the range of cells
Empty cell > Formula > Ctrl + Shift + Enter.
Step 2
Now to count the cells with question marks, use the formula =SUM(LEN(A2:A8)-LEN(SUBSTITUTE(A2:A8,"?",""))). and click on Ctrl, Shift, and Enter.
Empty cell > Formula > Ctrl + Shift + Enter.
This is how you can count wildcards in Excel.
Conclusion
In this tutorial, we have used a simple example to demonstrate how you can use wildcards (asterisks or question marks) in Excel to highlight a particular set of data.
63 Views
