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

Download Practice Workbook

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

5 Simple Methods to Count Cells with Specific Value in Excel

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?

Read More: How to Count Non Blank Cells with Condition in Excel (6 Methods)

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.

Read More: Excel Formula to Count Cells with Text (All Criteria Included)

Similar Readings

• Excel Count Number of Cells in Range (6 Easy Ways)
• Count Blank Cells in Excel (5 Ways)
• How to Count Empty Cells in Excel (4 Suitable Ways)
• Excel Count Cells with Numbers (5 Simple Ways)
• How to Count Cells That are Not Blank in Excel (8 Useful Methods)

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

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

Read More: How to Count Cells with Specific Text in Excel (5 Easy Ways)

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

Read More: How to Count If Cell Contains Number (Easiest 7 Ways)

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.

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. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.

Related Articles

• How to Count Filtered Rows with Criteria in Excel (5 Easy Ways)
• Count Only Visible Cells in Excel (5 Tricks)
• How to Count Filled Cells in Excel Using VBA (7 Methods)
• Count Number of Cells with Dates in Excel (6 Ways)
• How to Count Odd and Even Numbers in Excel (3 Easy Ways)
• Count Blank Cells in Excel with Condition (3 Methods)
• How to Count Filled Cells in Excel (5 Quick Ways)