Here’s a sample dataset that will be used to demonstrate how to use the COUNTIF function.

## Download to Practice

## 22 Examples of COUNTIF Excel

The **COUNTIF** function is an Excel function which counts cells in a particular range that meet a single criterion/condition. By using the **COUNTIF** function you can count cells that contain

➤**Dates
**➤

**Numbers**

➤

**Text**

The **COUNTIF** function also supports logical operators **(>,<,<>,=) **as criteria and you also can use the **wildcards** **(*,?)** for **partial matching**.

The syntax of the **COUNTIF** function is:

**COUNTIF(range, criteria)
**This function has two

**arguments:**

**range**and

**criteria**.

**range – **put the range to define one or more cells to count, such as **A1:A10**.

**criteria –** condition based on which you want to count the range. It defines the condition that tells the function of which cells to count. You can use a **number**,** text string**, cell reference or expression, etc.

**Example 1 – COUNTIF Formula for Text **

We’ll go for the exact match of the given text.

- Select a cell where you want to keep your resulting value. We selected the
**H4**cell. - Insert the following formula in the selected cell or into the
**Formula Bar**.

`=COUNTIF(B4:B13,H3)`

We selected the cell range **B4:B13 **as the **range.** For **criteria,** we put the cell reference **H3** that contains the text **Adam Smith** (*You can use the text directly in the formula or you can use a cell reference to specify this value. To use the text directly, use the double quote (“”) to enclose the text*).

The

**COUNTIF**function will count how many times the selected text value exists in the selected cell range.

- Hit Enter.

**Read More:** **COUNTIF Excel Example (22 Examples)**

**Example 2 – COUNTIF Formula for Numbers**

We’ll get the exact match of the given numbers.

- Insert the following formula in the cell where you want the result, then hit Enter. We chose cell H4.

`=COUNTIF(D4:D13,H3)`

We selected the cell range **D4:D13 **as **range.** For **criteria, **we used the reference **H3 **that contains the number **23,456.**

**Read More:** **How to Use COUNTIF Between Two Numbers (4 Methods)**

**Example 3 – COUNTIF Formulas with Wildcard Characters**

- Insert the following formula in the cell where you want the result, then hit Enter. We used the cell H4.

`=COUNTIF(B4:B13,"*Scott*")`

We selected the cell range **B4:B13 **as **range. **As **criteria, **we used the partial text **Scott **and used the wildcard character **(*) **around it. This means that we’ll accept any number of characters before and after the text.

**Read More:** **COUNTIF Multiple Ranges Same Criteria in Excel**

**Example 4 – Count Cells Beginning with Certain Characters**

The **wildcard **characters allow us to count cells beginning with certain characters while using the **COUNTIF **function.

- Insert the following formula in the cell where you want the result, then hit Enter. We chose cell H4.

`=COUNTIF(B4:B13,H3)`

The cell reference **H3 **contains the characters **Ad***. The wildcard character **(*) **is at the end, so the formula checks for Ad at the beginning.

**Example 5 – Count Cells Ending with Certain Characters**

- Insert the following formula in the cell where you want the result, then hit Enter.

`=COUNTIF(B4:B13,H3)`

With the wildcard character **(*) **in the first position and an **h** to follow, the **COUNTIF **function will count the text if it ends with the character **h**.

**Example 6 – COUNTIF for Non-Blank Cells **

- Insert the following formula in the cell where you want the result, then hit Enter.

`=COUNTIF(E4:E13,"<>"&"")`

The **criteria ****“<>”&”” **means **not equal** blank. The “” at the end signifies a blank value.

**Example 7 – COUNTIF Blank Example**

- Insert the following formula in the cell where you want the result, then hit Enter.

`=COUNTIF(E4:E13,"")`

**Criteria ****“” **means implicitly checks whether the cell is blank.

**Example 8 – COUNTIF Greater Than (>)**

You can use the logical operators in the **COUNTIF **function to get values greater than, less than, not equal, equal.

- Insert the following formula in the cell where you want the result, then hit Enter. We chose H4.

`=COUNTIF(D4:D13,">"&H3)`

- H3 has a value of 23,456, so you’ll get a count of values greater than
**23,456**.

**Read More: ****COUNTIF Greater Than and Less Than [with Free Template]**

**Example 9 – COUNTIF Less Than (<)**

- Insert the following formula in the cell where you want the result, then hit Enter.

`=COUNTIF(D4:D13,"<"&H3)`

- You’ll get the count of values less than
**87,045**.

**Example 10 – COUNTIF Equal To (=)**

- Insert the following formula in the cell where you want the result, then hit Enter.

`=COUNTIF(D4:D13,"="&H3)`

- You’ll get the count of the values equal to
**87,045**(value of H3).

*All logical operators also work for Dates*.

**Example 11 – Count Dates with Today (Current Date)**

- Insert the following formula in the cell where you want the result, then hit Enter.

`=COUNTIF(E4:E13,TODAY())`

TODAY() returns today’s datevalue.

- You’ll get the count of today’s date throughout the dataset.

**Read More:** **COUNTIF Date Is within 7 Days**

**Example 12 – Count Dates Less Than Today**

You can count the past dates from **TODAY **using the **less than (<)** in the **COUNTIF **function.

- Insert the following formula in the cell where you want the result, then hit Enter.

`=COUNTIF(E4:E13,"<"&TODAY())`

- You’ll get the count of dates before today’s date.

**Example 13 – Using COUNTIF to Count Dates Greater Than Today**

- Insert the following formula in the cell where you want the result, then hit Enter.

`=COUNTIF(E4:E13,">"&TODAY())`

- You’ll get the count of the upcoming dates of today’s date.

**Read More:**** How to Use COUNTIF to Count Cells Greater Than 0 in Excel**

### Example 14 – Count Dates that Are Due in a Week

- Insert the following formula in the cell where you want the result, then hit Enter.

`=COUNTIF(E4:E13,"="&TODAY()+7)`

TODAY() gets the today’s date, adding 7 to that gives the date in a week, and then appending “=” to the start changes the condition to a check for the exact date.

- You’ll get the count of the upcoming week dates.

**Read More:** **COUNTIF Between Two Dates in Excel**

### Example 15 – Using COUNTIF to Count Dates Within a Range

- Insert the following formula in the cell where you want the result, then hit Enter.

`=COUNTIF(E4:E13, ">=12/20/2021")-COUNTIF(E4:E13, ">12/24/2021")`

We used the **COUNTIF **function twice.

In the first **COUNTIF **function, the **criteria **used is **>=12/20/2021**.

In the second **COUNTIF **function, the **criteria **used is **>12/24/2021**.

We then subtract those values to count the dates that exist in between the given range.

- You’ll get the count of dates that fall in the specified range.

**Example 16 – Count Numbers within a Range**

- Insert the following formula in the cell where you want the result, then hit Enter.

`=COUNTIF(D4:D13,">20000")-COUNTIF(D4:D13,">=156789")`

Both COUNTIF functions check how many numbers are greater than a specific value. By subtracting the counts, we get a count of numbers between those values.

- You’ll get the count of numbers within the range.

**Example 17 – Multiple COUNTIFs to Count Cells with Multiple OR Criteria**

- Insert the following formula in the cell where you want the result, then hit Enter.

`=COUNTIF(B4:B13,G4)+COUNTIF(B4:B13,H4)`

Since both COUNTIF functions are checking in the same range, and we use the plus operator to sum the counts, a single value that satisfies both criteria will get counted twice. This is impossible in the sample (since one of the checks is exact and the other is partial for different values), but it’s something to keep in mind.

**Read More:** **How to Use Excel COUNTIF That Does Not Contain Multiple Criteria**

### Example 18 – Find Duplicate and Unique Values in One column

- Insert the following formula in the cell where you want the result for duplicate value checks, then hit Enter.

`=COUNTIF($B$4:$B$13,B4)>1`

- The
**COUNTIF**function will show**TRUE**for duplicate values and**FALSE**for unique values in the selected cell range.

- Use the
**Fill Handle**to**AutoFit the formula**in the rest of the cells.

- Insert the following formula in the cell where you want the result for the unique value count, then hit Enter.

`=COUNTIF(G4:G13,TRUE)`

- You’ll get the count of all duplicate values based on the check for value FALSE in column G.

**Read More:** **COUNTIF between Two Cell Values in Excel (5 Examples)**

**Example 19 – Using COUNTIF and SUMPRODUCT to Count Duplicate Values in a Row**

We transposed the values of the dataset. Here is the new dataset.

- Insert the following formula in the cell where you want the result, then hit Enter.

`=SUMPRODUCT((COUNTIF(C3:H3,C3:H3)>1)*(C3:H3<>""))`

- The
**COUNTIF**function will show the number of duplicate values in the selected cell range.

**Example 20 – Count Unique Values in a Row**

- Insert the following formula in the cell where you want the result, then hit Enter.

`=SUMPRODUCT((COUNTIF(C3:H3,C3:H3)=1)*(C3:H3<>""))`

- This
**COUNTIF**function will show all the unique values in the selected cell range.

**Example 21 – Using COUNTIF and SUMPRODUCT to Count Duplicates Between Two Columns**

We have a dataset of two **Name **lists.

- Insert the following formula in the cell where you want the result, then hit Enter.

`=SUMPRODUCT((COUNTIF(B4:B13,C4:C13)=0)*(C4:C13<>""))`

The COUNTIF function checks whether each cell in column C is not present in column B, then SUMPRODUCT multiplies that by checking if that cell isn’t blank. SUMPRODUCT loops through the entire column C this way.

- You’ll get the count of all duplicate values.

**Example 22 – COUNTIF on a Non-Contiguous Range of Cells**

We used a new dataset given below.

- Insert the following formula in the cell where you want the result, then hit Enter.

`=COUNTIF(D4:D13,H4) + COUNTIF(E4:E13,I4)`

In the first **COUNTIF **function, we selected the cell range **D4:D13 **as the **range **and used the cell reference of the **23456 **number for criteria.

In the second **COUNTIF **function, we selected the cell range **E4:E13 **as the **range,** then** **used **I4 **as the cell reference of the **87640 **number as the condition.

We added both **COUNTIF **functions to get the count of the used criteria in the selected cell.

- Here’s the result.

**Practice Section**

We’ve provided a practice sheet in the workbook to practice using COUNTIF.

## Related Articles

**Compare Two Tables and Highlight Differences in Excel (4 Methods)****COUNTIF vs COUNTIFS in Excel (4 Examples)****How to Use COUNTIF with SUBTOTAL in Excel (2 Methods)****VBA COUNTIF Function in Excel (6 Examples)****How to Use COUNTIF with WEEKDAY in Excel**