Excel COUNTIF Function to Count Cells Greater Than 0

Get FREE Advanced Excel Exercises with Solutions!

COUNTIF function is one of the most basic & simple operations in MS Excel that can be used to count 0 (Zero), greater than 0, or less than 0 under lots of criteria from multiple columns. In this article, I’ll try to guide you through the proper illustrations of how we can utilize this COUNTIF function precisely to determine the range of cells containing numbers greater than 0 (Zero).


Download Practice Workbook

You can download the Excel Workbook that we used to prepare this article. You can change the values, formulas, or input data in the blank cells to find out your own results.


Introduction to 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.

countif greater than 0


6 Ideal Examples of COUNTIF Function to Count Greater Than 0 (Zero)

To determine how many cells satisfy a requirement, we use the COUNTIF function. This is one of the statistical functions in Excel.


1. Count Cells Greater Than 0 (Zero) with COUNTIF

Now, here’s our dataset with goals & assists in 15 matches of a footballer in a season. He has not played 2 matches (Match 6 & 9) and the cells are blank there. We want to count how many goals he has scored.

📌 Steps:

  • Select output Cell F13 & type-
=COUNTIF(C5:C19,">0")
  • Press Enter & you’ll find the total of 9 matches he has scored.

countif greater than 0

NOTE: Keep in mind, while inputting the criteria for greater or less than a number in the COUNTIF function, you have to put it between Double-Quotes(““).

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


2. Add Ampersand(&) with COUNTIF Function to Count Cells Greater than 0(Zero)

We can also type our criteria for greater than zero by using Ampersand (&). As we’re now going to find how many matches the player has provided assists to the goal, we have to consider Column D now.

📌 Steps:

  • Type in Cell F13
=COUNTIF(D5:D19,">"&0)
  • Press Enter & you’ll see the footballer has assisted in 8 instances out of 15 matches.

countif greater than 0

Here, 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


3. Compute Cells Data Greater Than or Equal to 0(Zero) with Excel COUNTIF Function

Now we want to count cells containing numbers greater than 0. In our dataset, we can apply it to count the number of matches the footballer has played.

📌 Steps:

  • In Cell E13, we have to type-
=COUNTIF(C5:C19,">=0")
  • Then, press Enter & we’ll see the player has played a total of 13 matches as there are two blank cells in our dataset which have not been counted.

Read More: Count Blank Cells with Excel COUNTIF Function: 2 Examples


Similar Readings


4. And Less Than Another Number with COUNTIF to Count Greater Than 0 (Zero)

Here’s another case where we want to find a number greater than 0 but less than 2. For our dataset, we can use this logic to count the number of matches the player has scored only 1 goal.

📌 Steps:

  • In Cell F13, we have to type-
=COUNTIF(C5:C19,">0") - COUNTIF(C5:C19,">=2")
  • Press Enter & you’ll notice 5 matches the player has scored only 1 goal.

🔎 How Does The Formula Work?

First of all, we’re finding out how many matches he has scored & it’s 9 in total. Then, We’re determining the number of matches he has scored 2 or more goals & the number is 4. After subtracting the resultant value of 2nd criterion from the 1st one, we’ll get the total number of matches he has scored exactly 1 goal.

Read More: How to Apply COUNTIF Between Two Cell Values in Excel


5. Utilize COUNTIFS Function under Multiple AND Criteria from Different Columns

If we want to add more than one criterion while counting cells greater than 0, then we have to use the COUNTIFS function where multiple criteria can be added easily. So, now we want to know how many matches the footballer has scored goals as well as provided assists.

📌 Steps:

  • In Cell F13, type-
=COUNTIFS(C5:C19,">0",D5:D19,">0")
  • Further, press Enter & you’ll see the player has contributed to both goals & assists 7 times out of 15 matches.

NOTE: To add multiple criteria, we have to simply use Comma(,) to separate two criteria.

Read More: COUNTIF That Does Not Contain Multiple Criteria in Excel


6. Combine COUNTIF & COUNTIFS Functions under Multiple OR Criteria from Different Columns

And in our last example, we’ll use COUNTIF along with COUNTIFS functions together. This time we’re going to find the number of matches where the player has either scored goals or providing assists.

📌 Steps:

  • Firstly, in Cell F13, the formula for our criteria will be-

=COUNTIF(C5:C19,">0") + COUNTIF(D5:D19,">0") - COUNTIFS(C5:C19,">0",D5:D19,">0")

  • Now, press Enter & you’re done.
  • So, in a total of 10 matches, the footballer has either scored goals or provided assists out of 15 instances.

🔎 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. After that, 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


Concluding Words

I hope I’ve covered all possible criteria & methods we can use COUNTIF as well as COUNTIFS functions to count the cells greater than 0 in this article. If you think I’ve missed one that should’ve been added too then please let me know in the comment section. You can also have a look at our other interesting & informative articles related to Excel functions on this website.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

2 Comments
  1. 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.

      • Now, if we want to count the number of FALSE in the column C, it will return zero.

      • There are two solutions for this issue. Firstly, you can add asterisks (*) with the criteria.

      • Another way is to change the formula in column C to return TRUE or FALSE as Boolean values.

      • Then, the COUNTIF function will return the correct value.

      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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo