Count Text at Start with COUNTIF & LEFT Functions in Excel

Get FREE Advanced Excel Exercises with Solutions!

Counting text or characters is a very common phenomenon among regular Excel users. Especially when we work with a large dataset with various criteria. In Excel, we can use the COUNTIF function to count text from the left. Unfortunately, it is not possible to use the COUNTIIF and LEFT functions together to count a specific text at the start of a cell. But we can do the same with the COUNT, IF, and LEFT functions. In this article, we will show the procedure in easy steps. Apart from these, we can also apply some other functions to count text from the left.


Count Text at Start with COUNTIF & LEFT Functions in Excel

To describe the whole process, we will show the steps one after another for better understanding. So, without further delay, let’s follow the steps below.

STEP 1: Create Dataset for Specific Text

  • First, create a dataset in the Cell range B4:C15 with information of Keywords and their Search Volumes of different types of coffee makers.

COUNTIF LEFT Text


STEP 2: Insert the Text You Need to Count

  • Then, insert the Search text B in Cell F6 as we want to count how many cells have this letter at the beginning of a word in the dataset.


STEP 3: Apply Formula

  • After that, insert this formula in Cell F7.
=COUNT(IF(LEFT(B5:B15,1)="B",1,""))

COUNTIF LEFT Text

  • Lastly, press Enter to see the result.

Final Output

  • In the end, we have found the number of occurrences of the specific text at the beginning of a cell.

Here, the LEFT function finds the text from the left side of the Cell range B5:B15. Then the IF function determines the condition for counting the occurrence of specific character B. Finally, the COUNT function counts cells based on the given condition and returns a number. In the formula, 1 is used to determine that it will count cells that begin with one character.

Read More: How to Use COUNTIF Function to Count Text from List in Excel


Alternative Solutions for Counting Specific Text in Excel

You can also count characters or texts with the following solutions as well. For illustration, we will use the same dataset as above.

1. Combine SUMPRODUCT, LEFT & LEN Functions to Count Particular Text from Left

We can combine the SUMPRODUCT, LEFT, and LEN functions to count the number of the particular text.

  • To count text, first, apply this formula in Cell C18.
=SUMPRODUCT(--(LEFT(B5:B15,LEN(C17))=C17))

Combine SUMPRODUCT, LEFT & LEN Functions to Count Particular Text at Start

  • Afterward, press Enter.
  • Finally, you will get a similar output as counted text.

In this formula, the LEN function computes the length of the text string in Cell C17. Then, the LEFT function searches for the text in the left string of the Cell range B5:B15. Lastly, the SUMPRODUCT function calculates the total number of characters.

Read More: Excel COUNTIF to Count Cell That Contains Text from Another Cell


2. Apply COUNTIF Function to Return Number of Specific Text

Another solution is that we can use the COUNTIF function to count the number of specific texts. For this, go through this process.

  • First, insert this formula in Cell F7.
=COUNTIF(B5:B15,"*B*")

Apply COUNTIF Function to Return Number of Specific Word in Excel

  • Then, hit Enter.
  • That’s it, we get the accurate output as before for the counted number of the text.

In this formula, the COUNTIF function counts the specific text based on the condition provided.
  • Even, if you need to find any specific word other than character, you can apply this formula.
=COUNTIF(B5:B15,"*Best*")

  • Apart from this, you can also find multiple words with this formula.
=COUNTIF(B5:B15,"*Best Espresso*")

Read More: How to Use COUNTIF Function In Excel to Count Bold Cells


Things to Remember

  • Any of the methods above does not require formatting the output cell as Number. Because all the formulas return the value as a number.
  • If you are using an older version of Excel, then you have to press Ctrl + Shift + Enter to get the output as it is an array formula.

Download Practice Workbook

Get this sample file and practice by yourself.


Conclusion

Henceforth, I hope it was a helpful article for you on how to count text or characters with COUNT, IF, and LEFT functions in Excel. Also, we tried to explain two alternative solutions to count text. Let us know if you have any other methods to suggest for counting specific characters.


Related Articles


<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo