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

Suppose we have a dataset of the Book Name, Published Year, Author, and Genre of some books.

There are a variety of ways in which we can count single and multiple specific values from this table.

### Method 1 – Using COUNTIF Function

Steps:

• Select a cell (C16) and enter the following formula:
`=COUNTIF(E5:E14,"Biographical Novel")`

The COUNTIF function will count cells containing the text “Biographic Novel” inside the given range (E5:E14).

• Press ENTER and the correct output of 4 will be returned.

### Method 2 – Using a Combination of SUMPRODUCT and EXACT Functions

Steps:

• Select a cell (C16) and enter the formula below:
`=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.
• The output is 2.

• Click ENTER.

### Method 3 – Using SUMPRODUCT, ISNUMBER, and FIND Functions

Steps:

• Select a cell (C16) and enter the following formula:
`=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.
• The SUMPRODUCT function will sum the output from the corresponding cells.

• Press ENTER.

The correct output, 2, is returned.

### Method 4 – Using COUNTIFS Function for Multiple Criteria

To count specific values with multiple conditions, we can use the COUNTIFS function.

Steps:

• Select a cell (C16) and enter the following formula:
`=COUNTIFS(D5:D14,"Thomas Hardy",C5:C14,">1880")`

Where,

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

• Press ENTER.

Only 1 book matches our criteria. The correct result is returned.

### Method 5 – Using VBA

Steps:

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

The correct output is returned.

## Things to Remember

• The COUNTIF function has limitations in counting texts, with a maximum of 255 characters.
• Using a range of cells from another workbook as an argument may return a “#Value Error”.

<< Go Back to Count Cells | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF