How to Count Cells with Specific Value in Excel (5 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

While working in Microsoft Excel sometimes we need to count cells with specific values so that we can work more effectively. But it might seem difficult to you because of some limitations. Today, In this article I am sharing with you how to count cells with specific values in Excel.


How to Count Cells with Specific Value in Excel: 5 Simple Methods

In the following, I have described 5 simple and quick methods to count cells with a specific value in Excel.

Suppose we have a dataset of some Book Name, Published Year, Author, and Genre of those books. Now we will count single and multiple specific values from this table. Stay tuned!


1. Use of COUNTIF Function to Count Cells with Specific Value

If you are searching for a simple solution to count cells with specific values then you are at the right place. In this method, I am going to show you with the help of the COUNTIF function. Follow the instructions below-

Steps:

  • Simply, choose a cell (C16) and apply the below formula down-
=COUNTIF(E5:E14,"Biographical Novel")

Where,

  • The COUNTIF function will count cells with a given condition which is “Biographic Novel” inside the given range (E5:E14).

Use of COUNTIF Function to Count Cells with Specific Value

  • Next, press ENTER and you will get an output 4 inside the chosen cell.
  • So, here we have successfully counted cells with a specific text value. Simple isn’t it?

Use of COUNTIF Function to Count Cells with Specific Value


2. Combination of SUMPRODUCT and EXACT Functions

You can also try the combination of the SUMPRODUCT and EXACT functions to do the same work.

Steps:

  • First, choose a cell (C16) and write the below formula down-
=SUMPRODUCT(--EXACT("Charlotte Bronte",D5:D14))

Where,

  • The EXACT function will provide True or False output from the given cell range (D5:D14).
  • The SUMPRODUCT function multiplies the corresponding values and returns the sum of the product. Thus the output stands at 2.

Combination of SUMPRODUCT and EXACT Functions

  • Hence, click ENTER.
  • Finally, you will get cells counted with a specific value in Excel.


3. SUMPRODUCT, ISNUMBER, and FIND Functions to Count Cells with Specific Value

Here, I’ll use the combination of the SUMPRODUCT, ISNUMBER, and FIND functions to count cells with specific text or numeric values. Go through the steps below-

Steps:

  • Similarly, choose a cell (C16) and put the below formula down-
=SUMPRODUCT(--ISNUMBER(FIND("Dickens",D5:D14)))

Where,

  • The FIND function will search for the given character- “Dickens” from the cell range.
  • The ISNUMBER function will then check whether the cell range has numeric values or not.
  • To finish the SUMPRODUCT function will sum the output from the corresponding cells and thus the final result will be displayed 2.

  • Then, hit the ENTER button from the keyboard.
  • Within a glimpse of an eye, you will get the specific text values counted from the table.

Performance of SUMPRODUCT, ISNUMBER, and FIND Functions to Count Cells with Specific Value


4. Using COUNTIFS Function to Count Cells with Specific Value for Multiple Criteria

We can also use the COUNTIFS function if we want to count specific values with multiple conditions.

Steps:

  • In the same fashion, select a cell (C16) and apply the below formula-
=COUNTIFS(D5:D14,"Thomas Hardy",C5:C14,">1880")

Where,

  • The COUNTIFS function counts cells from multiple ranges counting cells with “Thomas Hardy” in cell range “D5:D14” and cell value more than “1880” in cell range “C5:C14”.

Using COUNTIFS Function to Count Cells with Specific Value for Multiple Criteria

  • Then, Hit ENTER.
  • In summary, we have gathered our final result by counting cells with multiple conditions.

excel count cells with specific value


5. VBA to Count Cells with Specific Value

Here I have explained another method of counting cells with specific values. Just go through the steps below-

Steps:

  • First, open the workbook and press ALT+F11 to open “Microsoft Visual Basic for Applications”.

  • In the new module, place the following code and click the “Run” icon-
Sub Count_Cells_with_Specific_Value()
Range("H5") = Application.WorksheetFunction.CountIf(Range("E5:E17"), Range("G5"))
End Sub

VBA to Count Cells with Specific Value

  • In conclusion, we have successfully counted cells with specific values in Excel. Enjoy!

VBA to Count Cells with Specific Value


Things to Remember

  • The COUNTIF function has limitations in counting texts. It can count more than or close to 255 characters.
  • While working you will get to see “#Value Error” if you take a range of cells from another workbook in its argument.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, I have covered all the methods to count cells with a specific value in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. Stay tuned and keep learning.


<< Go Back to Count Cells | Formula List | 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.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo