Download the Practice Workbook
Introduction to the COUNTIF Function
- Syntax
COUNTIF(range, criteria)
- Arguments
range: Range of cells to be selected.
criteria: Criteria of the cells that need to be assigned.
- Function
Counts the number of cells within the range that meet the given condition.
- Example
In the picture below, a list of color names is given. If we want to know how many times Red is there then we have to type in the output cell-
=COUNTIF(B2:B11,"Red")After pressing Enter, we’ll see there are 4 instances of Red in the list.
6 Examples of COUNTIF Function to Count Greater Than 0 (Zero)
Example 1 – Count Cells Greater Than 0 (Zero) with COUNTIF
We have a dataset of goals and assists in 15 matches of a footballer in a season. He has not played 2 matches (Matches 6 and 9) and the cells are blank there. We want to count how many goals he has scored.
Steps:
- Select output Cell F13 and insert:
=COUNTIF(C5:C19,">0")- Press Enter.
Read More: How to Use COUNTIF Between Two Numbers (4 Methods)
Example 2 – Add Ampersand (&) with the COUNTIF Function to Count Cells Greater than 0 (Zero)
Steps:
- Use the following formula in Cell F13:
=COUNTIF(D5:D19,">"&0)- Press Enter.
We’re using Ampersand(&) after Double-Quotes to join the “Greater Than” criteria with 0.
Read More: Excel COUNTIF with Greater Than and Less Than Criteria
Example 3 – Compute Cells with Value Greater Than or Equal to 0 (Zero) with Excel COUNTIF Function
Steps:
- In Cell E13, use:
=COUNTIF(C5:C19,">=0")- Press Enter.
Read More: Count Blank Cells with Excel COUNTIF Function: 2 Examples
Similar Readings
- COUNTIF Date Is within 7 Days
- How to Use the Combination of COUNTIF and SUMIF in Excel
- COUNTIF Between Two Dates in Excel (4 Suitable Examples)
- How to Use COUNTIF with WEEKDAY in Excel (3 Easy Ways)
- COUNTIF Excel Example (22 Examples)
Example 4 – And Less Than Another Number with COUNTIF to Count Greater Than 0 (Zero)
We want to find the number of values greater than 0 but less than 2.
Steps:
- In Cell F13, use
=COUNTIF(C5:C19,">0") - COUNTIF(C5:C19,"<=2")- Press Enter.
Read More: How to Apply COUNTIF Between Two Cell Values in Excel
Example 5 – Utilize the COUNTIFS Function under Multiple AND Criteria from Different Columns
We want to know how many matches the footballer has scored goals as well as provided assists.
Steps:
- In Cell F13, use
=COUNTIFS(C5:C19,">0",D5:D19,">0")- Press Enter.
Read More: COUNTIF That Does Not Contain Multiple Criteria in Excel
Example 6 – Combine COUNTIF and COUNTIFS Functions under Multiple OR Criteria from Different Columns
We’re going to find the number of matches where the player has either scored goals or provided assists.
Steps:
- In Cell F13, the formula is:
=COUNTIF(C5:C19,">0") + COUNTIF(D5:D19,">0") - COUNTIFS(C5:C19,">0",D5:D19,">0")
- Hit Enter.
How Does The Formula Work?
By using Plus (+) between two COUNTIF functions, we’re separately determining the number of matches the player has scored goals & provided assists. So, here the return value will be 9+8=17. The COUNTIFS function will find out how many matches the player has both scored goals & provided assists. Here the resultant count is 7. By subtracting the resultant value found through the previous step from that of 1st step, the final output will be 10 (17-7=10).
Read More: Apply COUNTIF Function in Multiple Ranges for Same Criteria
Related Articles
- Excel COUNTIFS Not Working (7 Causes with Solutions)
- How to Use COUNTIF with Wildcard in Excel (7 Easy Ways)
- VBA COUNTIF Function in Excel (6 Examples)
- How to Use COUNTIF for Date Range in Excel (6 Suitable Approaches)
- How to Use COUNTIF Function with Array Criteria in Excel
- Excel COUNTIF to Count Cells Greater Than 1 (2 Examples)
There are a load of numbers in my cells and I just get 0.
Thank you, Dan, for your comment. If the values are in text format, then the COUNTIF function will return zero. You can see that we have used a IF formula, which returns TRUE when the value from column B is greater than five. Notice the output is in text format.
However, if this doesn’t solve your problem, you can mail us your Excel file to: [email protected], and we’ll try to solve it as soon as possible.