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:

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:

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:

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:

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

Download Practice Workbook

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