It’s a common task for many Excel users to find out the count of cells that contains a value greater than 0.
Doing this manually would be time-consuming and error-prone. Thanks to some simple functions in Excel, you can easily count all the cells with values greater than 0.
In this tutorial, I will show you a couple of methods you can use to quickly count the total number of cells that have a value greater than 0 (which can quickly be done using the in-built COUNTIF function)
How to COUNTIF Greater Than 0 in Excel?
For this tutorial, I will be using the below data set that has two columns: column A has the names of the countries, and column B has the sales of each country.
I now need to figure out how many countries have sales that are greater than zero.
We’ll now go over a few different ways to count if sales figures are greater than zero.
Using Greater than Sign and Zero with Quotes
To figure out the number of countries having more than zero sales, we will first use the COUNTIF function with the greater than sign and zero with quotes.
Below is the formula that will count the number of countries with sales greater than 0.
Explanation of the formula:
The syntax of the COUNTIF function is COUNTIF(Range, Criteria).
The range refers to where you want to look, and the criteria refer to what you wish to look for.
According to this syntax, the range is cells B2 to B7 and “>0” is the criteria. We must type the logical operator greater than sign “>” and zero within double quotes.
The above formula gives the results as 0, which tells us that there are three cells in column B where the value is greater than 0
Using Greater Than Sign and Zero with Ampersand Sign
In the above example, we hard-coded the value 0 within the formula itself.
However, you can also use a cell reference with a value instead of hard coding the value in the formula. the benefit of this is that as soon as you change the value in the cell, the formula will automatically update and give you the result based on the value in that cell.
For example, if you change the value of the cell from zero to 10, the formula will give you all the cells with a value greater than 10
In this situation, we must use an ampersand between the logical operator and the cell reference. Only the logical operator – greater than sign should be enclosed in quotation marks.
Let’s use this new method to see how many countries have sales that are greater than zero.
Below is the formula using the ampersand operator that will count the number of countries with sales greater than 0.
In the above COUNTIF formula, the first argument is the range of cells in which you want to count the cells that have a value greater than 0.
The second argument is the criteria used for counting, and in this, we have three parts:
- “>” – the greater than logical operator that needs to be within double quotes
- & – the ampersand sign, which would connect the logical operator with the cell reference
- E1 – the cell reference whose value would be used in the criteria (in this example since we want to count the sales value greater than zero, we have entered zero in cell E1)
Also read: How to COUNTIF Partial Match in Excel?
COUNTIF Greater Than Zero for Non-contiguous Range
Assume we need to count the number of countries with more than zero sales for the below data set.
We cannot use the COUNTIF function on non-adjacent cells directly. Because the COUNTIF function only allows you to provide one cell range for the first argument.
There are two options for dealing with this problem.
Solution 1 – Using Multiple COUNTIF Functions
The easiest method is to apply the COUNTIF function separately for each range and add the results.
So, let’s enter 2 separate COUNTIF functions for 2 ranges and combine them with a plus sign.
Below is the formula with 2 COUNTIF functions that will count the number of countries with sales greater than 0.
In this formula, two COUNTIF functions are combined with a plus sign.
The result of the first COUNTIF function is 3, and the result of the second COUNTIF function is 5. The sum of the two results is 8.
Solution 2 – Using COUNTIF with INDIRECT
The INDIRECT function is the next answer for the problem of a non-contiguous range of cells.
We can generate a range of arrays with the help of the INDIRECT function.
Below is the formula for a non-contiguous range of cells that will count the number of countries with sales greater than 0.
Important things to remember for the above formula:
- You have to manually type each range as you cannot select the range for the formula.
- You have to type each range within quotes.
- You have to put a curly bracket for the entire range.
In this formula, the INDIRECT function is used to create an array of ranges. The COUNTIF function will calculate the count of the two ranges separately.
The sum function helps to combine the counts of the two ranges into one cell.
You need to be cautious when using the INDIRECT function, as it is volatile, which means that it recalculates every time you make any change in the worksheet. So if you have a lot of formulas in your worksheet, using the INDIRECT function may slow down your Excel file
Countif Greater Than Zero for Different Excel Workbooks
We can also use a range from another workbook to generate a COUNTIF function.
However, you must make sure that when the workbook with the formula is reopened, the second workbook containing the ranges must also be opened.
If it is not open, the function will produce a #VALUE! error.
Therefore, it is prudent to refrain from using the COUNTIF function for ranges in other workbooks.
This lesson explained how to use the COUNTIF function in two different ways to count values greater than zero, how to apply those two methods when the ranges are not adjacent, and how to prevent the #VALUE! error when the ranges for the function are from a different workbook.
In Excel, you can now easily count values greater than zero.
Other Excel articles you may also like:
- Count the Number of Yes in Excel (Using COUNTIF)
- How to Use Greater Than or Equal to Operator in Excel Formula?
- How to Count Unique Values in Excel (5 Formulas Methods)
- How to Compare Two Columns in Excel (using VLOOKUP & IF)
- ‘Does Not Equal’ Operator in Excel (Examples)
- How to Add Leading Zeros in Excel?
- How to Count Cells with Text in Excel?
- Count Cells that are Not Blank in Excel (Formulas)
- How to Count Between Two Numbers in Excel?