Count Greater than 0 in Excel (COUNTIF – COUNTIFS)

- Written by Puneet

excelchamps-free-courses-puneet-gogia

Let’s say you’re a sales manager keeping track of your team’s progress. You’ve got a list of how many sales store made makes every day. Now, you want to see how many days store sold something, ignoring the days they didn’t sell anything.

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.

Apart from this, in this tutorial we are going to explore more method that you can use to count cells below the value zero. So let’s get started…

COUNTIF Greater Than 0 (Formula)

In this example, we have a list of numbers with all the negative numbers and some positive numbers. Now, you need to count the numbers that are greater than 0.

  1. First, type “=COUNTIF” and start parentheses in cell C1 where we are going to get the result.
    excel-count-greater-than-zero-enter-function-in-cell
  2. Next, in the range argument specify the range (A2:A19) from which you want to count the numbers.
    excel-count-greater-than-zero-select-range
  3. After that, type a comma to move to the next argument.
  4. In the end, enter “>0” in the criteria argument and closing parentheses.
    excel-count-greater-than-zero-enter-criteria
=COUNTIFS(A2:A19,">0")

When you hit enter, you will get the result in cell C1. This formula returns 11.

excel-count-greater-than-zero-hit-enter-1

How this Formula Works

To understand this formula, you need to split it into two parts:

  • A2:A19: This is the cell range from which we count the values less than zero. To change this formula per your requirement, you can edit it to match the range in your workbook.
  • “>0”: We are applying this criterion to the range. You are telling the formula to count cells with a value greater than zero and ignore all the values below and equal to zero.

COUNTIFS to Count Greater than Zero and Less than 10 (Multiple Criteria)

If you want to count numbers greater than zero or less than 10 or any other number, then use the COUNTIFS function. Below is the formula that you can use:

=COUNTIFS(A2:A19,">0",A2:A19,"<10")
excel-count-greater-than-zero-less-than

This formula is simple yet powerful, so let’s split it into parts and understand it: A2:A19 is like our list of numbers. “>0”, tells the formula only to count numbers bigger than zero, ignoring the rest.

Then, “<10” adds another rule; it tells the formula only to count numbers less than 10. No matter which formula you are using, make sure to have the values in the column in the right data type, if number are store as text, then none of these method will work.

Other Formulas that can COUNT Cells Above Zeros

Yes, there are a few other ways that you can use to count cells above 0.

1. Using SUMPRODUCT

SUMPRODUCT can count the number of cells in the range A2:A119 that contain values greater than zero. This formula below multiplies items in arrays together and then adds them up.

=SUMPRODUCT(--(A2:A19>0))

A2:A100>0 checks if each number in the range is greater than zero, giving us a list of TRUEs and FALSEs. The “–” turns the TRUE and FALSE results into ones and zeros by comparing (A2:A100>0).

So, we end up with 1s for numbers above zero and 0s for numbers equal to or below zero. Finally, SUMPRODUCT adds up all the 1s to get the count of cells above zero as a value.

2. Using COUNT + FILTER

This formula filters the range A2:A119 to only include values greater than zero using the condition A2:A19>0 and then counts the filtered values using the COUNT.

=COUNT(FILTER(A2:A19, A2:A19>0))

FILTER takes values from a range that meets the condition. A2:A19 is the range of numbers we’re looking at. A2:A19>0 checks if each number in the range is greater than zero, and FILTER only keeps those values.

Then, COUNT simply counts the number of numbers left after filtering. So, the formula counts the number of values in the range A2:A19 that are above zero.

3. Using SUM + IF

You can use This array formula to count values above zeros.

=SUM(IF(A2:A19>0,1,0))

The IF function checks each cell in the range A2:A19. If the value in a cell is greater than zero, it returns 1; otherwise, it returns 0. Then, the SUM adds up all the 1s, effectively counting the number of cells with values above zero.

Last Updated: May 04, 2024

1 thought on “Count Greater than 0 in Excel (COUNTIF – COUNTIFS)”

Leave a Comment