Home / Excel Formulas / Count Greater Than 0 (COUNTIF) To count values that are greater than zero (0) from a list of values or a range of
cells, you can simply use Excel’s COUNTIF function using greater than zero criteria. COUNTIF is part of the statistical functions. Here we have a list of numbers ranging from -10 to 10 and you need to count the numbers which are greater than zero from this list.
When you hit enter, you will get the result in cell C1. And here this formula returns 11 in the result.  COUNTIFS to Count Greater than Zero and Less than 10And if you want to count numbers that are greater than zero or less than a 10 or any other number then you need to use the COUNTIFS function. Below is the formula that you can use: =COUNTIFS(A2:A19,”>0″,A2:A19,”<10″) In the above formula, as I said, you have the COUNTIFS function that allows you to refer to multiple ranges and specify multiple criteria.
Comparison operators > (greater than) is used in the expression to check the criterion with a numeric value. Whole criterion expression must be enclosed in double quotation marks, such as “>80.” So the active cell will contain formula like;
But if criteria value is provided in a cell reference, then criterion expression can consist of a comparison operator and cell reference. In this case, the comparison operator and cell reference are joined together by using an ampersand (&), which is a concatenating operator, such as “>”&C2 Here, comparison operator must be enclosed in double quotation marks, while cell reference should not be enclosed in double quotation marks. So the active cell will contain formula like;
Now COUNTIF function will count the number of cells in the selected data range that contain a numeric value greater than the specified numeric value in criterion expression and will return the result as a number. In this article, we have a student scorecard and want to count the number of students that have a score greater than a specific score as criteria. Suppose we want to count the number of students who have scored greater than 80 marks, and we use COUNTIF function in active cell F3 to show the results as a number, like;
Here, cell range C3:C12 contains the scoring marks of all the students, and we want to test these scores against criteria, greater than 80 marks, in double quotation marks in criterion expression. COUNTIF function counts the number of cells that contain a value greater than 80 and return the output as number as shown below. Here, you can see COUNTIF has returned the results as a number, representing only three students who have scored more than 80 marks, as highlighted above. We can also mention our criteria value in a cell reference and we can put this cell reference in criterion expression along with comparison operator, greater than (>), joined by ampersand operator (&). We have placed our criteria value in cell reference E3, and we have placed COUNTIF function in active cell F3 to show the result.
You can see comparison operator is enclosed in double quotation marks, and cell reference is without double quotation marks, both joined by an ampersand (&), in criterion expression of COUNTIF function. Both methods are easy, but the cell reference approach is handier because you can easily modify your criteria value in cell reference without editing your COUNTIF function. It will update the results based on the criteria entered in the cell reference. Like, if you want to count the students who have scored more than 70 marks instead of 80, then you can easily insert this new criterion in cell reference E3 and COUNTIF function will update the results accordingly. Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. |
