The COUNTIF Function in Excel: Syntax
➤ Description
Count cells within specific criteria.
➤ Generic Syntax
COUNTIF(range,criteria)
➤ Argument Description
| ARGUMENT | REQUIREMENT | EXPLANATION |
|---|---|---|
| range | Required | The number of cells we want to count according to the criteria. |
| criteria | Required | The criteria that we will use to determine which cells to count. |
➤ Returns
The return value of the COUNTIF function is numeric.
➤ Available in
Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.
Method 1 – Using the COUNTIF Function to Count Cell Numbers Between Two Numbers
We have a dataset of 6 students with their marks. We will count how many students have the marks that fulfill the following conditions: ‘>=70’ and ‘<80’. These conditions are placed as strings in two cells.
- Select cell F7.
- Insert the following formula:
=COUNTIF(C5:C10,">="& 70)- Press Enter.
- Insert the following formula in cell F8:
=COUNTIF(C5:C10,"<"& 80)- Press Enter.
- Finally, this will return the number of students 3 in cell F8.
Read More: COUNTIF Function to Count Cells That Are Not Equal to Zero
Method 2 – Using the COUNTIF Formula between Two Number Ranges
We will use the dataset of our previous example. We’ve inserted other conditions as text in the result table.
- Select cell F7.
- Insert the following formula:
=COUNTIF(C5:C10,">="&C12)-COUNTIF(C5:C10,">="&C13)- Press Enter. This returns the number of total students with marks in the range >=50 and <=80, which is 3.
- Insert the following formula in cell F8:
=COUNTIF(C5:C10,">="&40)-COUNTIF(C5:C10,">="&60)- Use this formula in cell F9:
=COUNTIF(C5:C10,">="&70)-COUNTIF(C5:C10,">="&90)- Here are our results.
How Does the Formula Work?
- COUNTIF(C5:C10,”>=”&C13): Calculates the number of students with more than 80 marks.
- COUNTIF(C5:C10,”>=”&C12): This part gives the student’s count who got more than 50 marks.
- COUNTIF(C5:C10,”>=”&C12)-COUNTIF(C5:C10,”>=”&C13): Returns the total count of students within the range >=50 & >=80.
Read More: Apply COUNTIF Function in Multiple Ranges for Same Criteria
Similar Readings
- How to Use COUNTIF for Date Range in Excel (6 Suitable Approaches)
- COUNTIF Date Is within 7 Days
- How to Use COUNTIF Function in Excel Greater Than Percentage
- VBA COUNTIF Function in Excel (6 Examples)
- How to Use Excel COUNTIF That Does Not Contain Multiple Criteria
Method 3 – Applying the Excel COUNTIF Function Between Two Dates
We have a dataset of dates with corresponding sales data. We are going to count the dates between two dates as well as for a single date.
- Select cell F7.
- Insert the following formula:
=COUNTIF(B5:B10,">="&C12)- Hit Enter.
- Put the below formula in cell F8:
=COUNTIF(B5:B10,">="&C12)-COUNTIF(B5:B10,">="&C13)- Press Enter.
How Does the Formula Work?
- COUNTIF(B5:B10,”>=”&C13): Counts the number of dates less than the value of cell C13.
- COUNTIF(B5:B10,”>=”&C12): Finds the total number of dates less than cell C12.
- COUNTIF(B5:B10,”>=”&C12)-COUNTIF(B5:B10,”>=”&C13): Returns the number of dates within the range >=10-01-22 and <=12-01-22.
Read More: COUNTIF Between Two Dates in Excel (4 Suitable Examples)
Method 4 – Applying the COUNTIF Function to Count a Particular Time Between Two Numbers
In the following figure, we have 3 time ranges. Let’s calculate the number of dates for each time range.
- Select the cell G7.
- Insert the following formula:
=COUNTIF(C5:C10,">="&F7)- Press Enter.
- Put the formulas given below in cells H8 and H9.
- For H8:
=COUNTIF(C5:C10,">="&F8)- For H9:
=COUNTIF(C5:C10,"<="&F8)- Here are our results.
Read More: How to Use Excel COUNTIF Between Time Range (2 Examples)
Download the Practice Workbook
Further Readings
- COUNTIF Excel Example (22 Examples)
- How to Use COUNTIF to Count Date Less Than Today in Excel
- Excel COUNTIF with Greater Than and Less Than Criteria
- How to Apply COUNTIF Between Two Cell Values in Excel
- Excel COUNTIF Function to Count Cells Greater Than 0
- How to Use COUNTIF for Non Contiguous Range in Excel
There seems to be a logical error when counting cells with values between two given numbers.
The result “3”, pshowed in the students’ marks example (>=50 and <=80) should be 4…
Thanks for your feedback sir.
The range is (>=50 and <=80). In Excel COUNTIF function by default omit the upper end value.
So, the formula is basically showing the result for the values less than 80.