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
Combine the COUNTIF and LEFT functions to count cells that begin with a certain character in Excel
Excel's COUNTIF function is used to determine the number of cells inside a given range that fulfil the requirements of a certain condition. In the criteria, logical operators such as ">, <, =" and wildcards such as "*" and "?" can be used to perform partial matching. Criteria can also be based on a value derived from another cell. In Excel, the COUNTIF function is one of a collection of eight other functions that divide logical criteria into two pieces that is range and criteria. Due to this, the syntax that is used to build criteria has been altered, and because COUNTIF requires a cell range, you are unable to utilize an array with it.
There is only provision for one condition in the COUNTIF expression. Utilize the COUNTIFS function when you need to apply numerous criteria at the same time.
In this tutorial we are going to learn how we can count the cells that begins with a specific character in a cell.
Count Cells using Formula
In Excel, you can use the COUNTIF function with the LEFT function to accomplish the task of counting the number of cells that begin with a particular character. This may be done quickly and easily.
Let’s go through an example with step by step.
Step 1
In our example, we have some name of the employees in our Excel sheet as shown in the following image. Let’s suppose we want to calculate the name of the employees whose name starts with “P”.
Step 2
Select one blank cell and write the following formula in the formula bar and then press Ctrl + Shift + Enter button to see the result. In our example we have selected B2 cell to store the counted value.
=COUNT(IF(LEFT(A2:A10,1)="P",1,""))
In the above formula, The LEFT function takes a certain number of characters from the left side of a text string that is provided to it. The number of characters to extract is set by the second argument. If number of characters are not given, it is set to 1 by default. If the number of characters is bigger than the number of available characters, LEFT returns the whole text string.
After providing the formula, we have to press Ctrl + Shift + Enter which is used to perform calculations with array formula.
See the following image in which the count of cells has been performed using the above formula.
2K+ Views
