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

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-

Where,

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

• 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?

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-

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.

• 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-

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.

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-

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”.

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

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

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

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