Organizing data using various formulas is quite common in Microsoft Excel. Usually, cells are organized as a table with rows and columns. In some cases, you might need to count a set of your data. The **COUNTIF** function can do the job for you. You can also use **COUNTIF** between two cell values in Excel to get results of a complex formula. This article will tell you eventually the varieties of uses available using **COUNTIF**. You will find 6 methods to help you with it. Not only **COUNTIF** itself will be used as multiple usages, but also get combined with other functions. It will be added with other functions as well. All the methods have step-by-step instructions with a figurative description.

## 📁 Download Excel File

Download the Excel file below.

## Overview of COUNTIF Function

**The COUNTIF function** counts the number of cells within a range that meet the given condition. The syntax of the formula is discussed below.

**=COUNTIF(range,criteria)**.

Here, “**range**” states the cells where you want to look.

Again, “**criteria**” calls what you want to look for.

## Learn to Use COUNTIF Between Two Cell Values with These 6 Effective Examples

The article is going to six types of examples of **COUNTIF** between two cell values in Excel. The process will help you understand the uses elaborately. The applications will be covering multiple uses of the **COUNTIF** function for values, time, **OR** function, combination with **SUMPRODUCT** function, and finally comparison. Therefore, users will get a walkthrough of these solutions to one of the most useful functions in Excel. A dataset of final exam status will be counted in the following methods to help you understand.

**Example**

### 1. Using COUNTIF to Count Cell Values

Counting cell values is one of the most used actions by **COUNTIF**. Values related to conditions can be added and utilized.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, select cell
**E6**. - Second, enter the required score to apply.
- Third, select cell
**E9**. - Fourth, type the following formula given below.

**=COUNTIF(D6:D9,”>=”&E6)**

- Finally, you should get 2 as there are two scores that are more than 65, according to the condition. The formula, range, and criteria are described in the image below.

**Example**

### 2. Applying COUNTIF to Count by Time

Counting cells using **COUNTIF** is very basic. But cells can be counted based on time as well.

⬇️⬇️ **STEPS **⬇️⬇️

- Initially, select cell
**E6**. - Next, enter the required time to evaluate.
- Now, select cell
**E9**. - After that, type the following formula given below.

**=COUNTIF(B6:B9,”>=”&E6)**

- Here, you should get 3 as there are three-time values that are more and equal to 11 AM, according to the condition. The formula, range, and criteria are described in the image below.

**Example**

### 3. Utilizing COUNTIF to Count by Date

You can count cells based on date as well.

⬇️⬇️ **STEPS **⬇️⬇️

- In the beginning, select cell
**E6**. - Now, enter the required date.
- Now, select cell
**E9**. - After that, type the following formula given below.

**=COUNTIF(B6:B9,”>=”&E6)**

- Here, you should get 2 as there are two date values that are later and equal to the 20th of March, 2022, according to the condition. The formula, range, and criteria are described in the image below.

**Example**

### 4. Employing COUNTIF as OR Function

**The OR function** is used to determine if any condition in a test is true. For certain calculations, you can filter out values using the **COUNTIF** function the same functionality as the **OR** function. You can see a problem in the images and description below where students with the names “Kristen” and “Charles” need to be counted.

⬇️⬇️ **STEPS **⬇️⬇️

- First, select cell
**E9**. - After that, type the following formula given below.

**=COUNTIF(C6:C9,”Kristen”)+COUNTIF(C6:C9,”*Charles”)**

- Here, you should get 2 as two name values consisting of “Kristen” and two names with “Charles”, according to the condition. The formula, range, and criteria are described in the image below.

**🔨 ****Formula Breakdown**

**COUNTIF(C6:C9,”Kristen”)+COUNTIF(C6:C9,”*Charles”)**

👉 Here, **COUNTIF(C6:C9,”Kristen”) **returns the number of data that has “Kristen” from the **C6:C9** range.

👉 Finally, **COUNTIF(C6:C9,”*Charles”)** returns the number of data that has “Charles” in the later part of cells from the data range **C6:C9**.

**Example**

### 5. Combining SUMPRODUCT and COUNTIF Functions

**The SUMPRODUCT function** returns the sum of the products of corresponding ranges or arrays. Combining it with the **COUNTIF** function can help you fulfill a complex condition.

⬇️⬇️ **STEPS **⬇️⬇️

- Initially, select cell
**E6**. - After that, type the following formula given below.

**=SUMPRODUCT((COUNTIF(D6:D9,D6:D9)>1)*(D6:D9<>””))**

- Here, you should get 2 as there are two sets of duplicate scores, according to the condition. The formula, range, and criteria are described in the image below.

**🔨 ****Formula Breakdown**

**SUMPRODUCT((COUNTIF(D6:D9,D6:D9)>1)*(D6:D9<>””))**

👉 Here, **COUNTIF(D6:D9,D6:D9)>1** looks for the data **D6:D9**, from the range **D6:D9 **itself and records if data is found more than once.

👉 Finally, the output is **2** as duplicate scores because here we got two **67**.

**Example**

### 6. Applying COUNTIF for Multiple Conditions

The **COUNTIF** function can handle a range and criteria. However, adding two **COUNTIF** functions or subtracting can help you get through many unnecessary hassles.

⬇️⬇️ **STEPS **⬇️⬇️

- In the beginning, select cell
**E9**. - Then, type the following formula given below.

**=COUNTIF(D6:D9,”<=70″)-COUNTIF(D6:D9,”<60″)**

- Here, the function gets the subtraction result of the two answers. Therefore, you should get 3 (4-1) as the result, according to the condition. You can notice the formula, range, and criteria in the image below.

**🔨 ****Formula Breakdown**

**COUNTIF(D6:D9,”<=70″)-COUNTIF(D6:D9,”<60″)**

👉 Here, **COUNTIF(D6:D9,”<=70″)** returns four scores that are less than or equal to 70.

👉 Moreover, **COUNTIF(D6:D9,”<60″)** returns one score that is lower than 60.

## How to Use COUNTIF Function in Excel with Multiple Ranges

The **COUNTIF** function can follow one range to look for the given criteria and count the value. Additionally, **COUNTIF** can work to arrange conditions for multiple ranges through mathematical operations.

⬇️⬇️ **STEPS **⬇️⬇️

- In the beginning, select cell
**E9**. - Then, type the following formula given below.

**=COUNTIF(D6:D9,”<=70″)*COUNTIF(C6:C9,”Kristen*”)**

- Here, there are four scores that are less than or equal to 70. Also, there are two name values that begin with Kristen. The function gets the multiplication result of the two answers. Therefore, you should get 8 (4*2) as the result, according to the condition. You can notice the formula, range, criteria in the image below.

## 📄 Important Notes

🖊️ You need to pre-format all the data. It will help you understand the outcome after the application or formula.

🖊️ For characters in between, you should add an asterisk before or after the name considering what you are looking for.

## 📝 Takeaways from This Article

The article allows the readers to understand the variety of options available to use COUNTIF between two cell values in Excel.

📌 You can count cell values from one range by applying value-based conditions from another cell. It will get you to the desired result.

📌 You need to format time values carefully. You can use range and criteria properly, COUNTIF for two cell values as well.

📌 You can evaluate date values as well, with a given date condition.

📌 **COUNTIF** can work as the **OR** function. You can get the expected data return from the **COUNTIF** function through a brief understanding of conditions.

📌 The **SUMPRODUCT** function combined with **COUNTIF** can work on a higher level of complexity.

📌 You can even work on multiple criteria on one cell through multiple **COUNTIF** functions.

## Conclusion

You can use **COUNTIF** between two cell values in Excel, five combinations for values, time, **OR** function, combination with **SUMPRODUCT** function, and finally comparison. In case of further queries, we request readers to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow **ExcelDen** to get more access to solutions regarding your Excel problems.