6 Examples to Use COUNTIF Between Two Cell Values in Excel

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.

Time COUNTIF between two cell values


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.

As OR function - COUNTIF between two cell values

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

Multiple criteria - COUNTIF between two cell values

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

(Visited 116 times, 1 visits today)
Farihat

Farihat

An Excel enthusiast who wants to share and loves to solve Excel problems. I try to explore available options to guide readers to the maximum ways how they can solve. The world is limitless, and so are the problems in it. Let's help each other to grow. I would love to hear feedback and queries from readers. You can visit my LinkedIn for further information.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo