Countif greater than 0 Google Sheets

At its core, countif is super simple: it counts every cell that meets a condition. Countif greater than 0 is just one of the many applications of this awesome function. 

In this article we will see just how powerful this tool is and use it to the very limits of its capabilities. Even if you’ve used this function before you will surely find something new to try out.

Countif Syntax for Google Sheets

The structure for countif is as simple as it comes.

=COUNTIF(range, criterion)

  • Range – cells for countif to test the criterion against.
  • Criterion – test to apply to the range.

And that’s it, as simple as a function can get. But as we’ll explore there is more than meets the eye.

Connect, merge, filter or split your spreadsheets

Different applications of countif

Let’s go over a handful of applications of this great function. Some of them include other functions, and others even have alternative functions with the same functionality.

Countif greater than 0

To count every number value over 0 all you have to do is use the greater than operator (“>”).

=COUNTIF(A2:A11,”>0″)

As you can see below, this won’t match exactly “0” or anything that isn’t a number.

countif greater than 0 - 1

Countif not blank

Here we will use the not equal to operator (“<>”) followed by nothing. This will count all cells that actually hold data. This can also be achieved with the counta function which is much simpler.

This formula is particularly useful when trying to assess how many data points are in a spreadsheet.

=COUNTIF(A2:A11,”<>”)

As expected this only counts 5 cells with data in the range.

countif greater than 0 - 6

Countif does not equal

Just like in the previous example this can be achieved with the not equal to operator (“<>”). Only values other than the one specified will be counted.

=COUNTIF(A2:A11,”<>0″)

Using the same dataset in the count greater than 0 example, 8 cells were matched.

countif greater than 0 - 5

Countif contains partial text

If you want to count cells with partial text matches, then you have to use the “*” wildcard. This will match any string values.

=COUNTIF(A2:A11,”*New*”)

In this example we matched only cells with the word “new” and counted states which include it in their name.

countif greater than 0 - 4

Countif month

This is where things start getting complex. This operation requires two new functions where the range would have been:

  • MONTH – Extracts the month from a date and returns it as a number value. In this case we want to match 3 which is March.
  • ARRAYFORMULA – Ensures that the MONTH function is applied to each cell in the range, rather than just the first cell.

=COUNTIF(ARRAYFORMULA(MONTH(A2:A11)), 3)

This formula matched 3 dates in the spreadsheet. Feel free to change the month function for its year or day counterparts to match different parts of a date. Make sure to also change the range and the expected value accordingly.

countif greater than 0 - 3

Google Sheets countif multiple criteria

Unfortunately, the only way to test against multiple criteria is to add the results from two separate countif functions. Clearly, this isn’t very elegant and you are much better off using countifs. Still it is worth trying this to remember that you can add the results of different functions.

=COUNTIF(A2:A11, “=5”) + COUNTIF(A2:A11, “=0”)

countif greater than 0 - 2

Get counting!

Now that you know various ways to count cells based on specific criteria you can count to your heart’s content. The countif formula is a great stepping stone on your spreadsheet journey.

Check out Sheetgo if you want to learn how you can connect spreadsheets and automate your business processes.

Ready to streamline your spreadsheet data?

You may also like…