COUNTIF Greater Than Zero in Excel

by

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.

data set where we need to countif greater than 0 in Excel

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.

=COUNTIF(B2:B7,">0")
COUNTIF function to count cells 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

Also read: Count Cells Less than a Value in Excel (COUNTIF Less)

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.

=COUNTIF(B2:B7,">"&E1)
COUNTIF function with ampersand and cell reference

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.

data in two columns

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.

=COUNTIF(B2:B7,">0")+COUNTIF(E2:E7,">0")
Using Multiple COUNTIF Functions

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.

=SUM(COUNTIF(INDIRECT({"B2:B7","E2:E7"}),">0"))
Using COUNTIF Function with Indirect

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

1 thought on “COUNTIF Greater Than Zero in Excel”

  1. I need to have my total column state if a set of number is over 18o in 10 month or 225 in 2 of those months then the total will be 180 or 225 max. I cannot figure out the If/or formula. Can there be two parameters in one cell to return the calculation of 5 columns of numbers return the correct if/or formula or would it be an if/or/and formula? Any help with this would be greatly appreciated.

    Col 1 Col 2 Col 3 Col 4 Col 5 Total Pmt
    40 45 40 40 50 175 218.75

    I need the total cell reflect that any hours over 45 be calculated at the 45(max contribution rate) but still see actual hours entered in columns 1-5. I have set up the Pmt column with total hours x 1.25 but the Total is where I should have the if/or? statement. I hope I explained this correctly. Thank you for any help you can provide.

    Reply

Leave a Comment