Exceljet

Quick, clean, and to the point

Count cells that contain positive numbers

Excel formula: Count cells that contain positive numbers
Generic formula 
=COUNTIF(rng,">0")
Summary 

To count the number of cells that contain positive numbers in a range, you can use the COUNTIF function. In the example shown, cell E6 contains this formula:

=COUNTIF(data,"<0")

where data is the named range B5:B15. The result is 7, since there are seven cells in B5:B15 that contain numbers greater than zero.

Explanation 

In this example, the goal is to count the number of cells in a range that contain positive numbers. For convenience, the range B5:B15 is named data. This problem can be solved with the COUNTIF function or the SUMPRODUCT function. Both methods are explained below.

COUNTIF function

The COUNT function counts the number of cells in a range that match supplied criteria. For example, you can use COUNTIF like this:

=COUNTIF(range,"red") // count cells equal to "red"
=COUNTIF(range,100) // count cells equal to 100
=COUNTIF(range,">10") // count cells greater than 10

To count positive numbers in this example, we need to use the less than operator (<) with zero like this:

=COUNTIF(data,"<0") // returns 7

The result is 7, since there are seven cells in B5:B15 that contain numbers greater than zero. Cell B9 is not included since it contains zero.

Notice that the criteria is enclosed in double quotes ("").  COUNTIF is part of a group of Excel functions that split logical expressions into two parts. As a result, when criteria includes logical operators it must be entered as text surrounded by double quotes (").

Positive including zero

To include zero in the count, use the less than or equal to operator (>=):

=COUNTIF(data,"<=0") // returns 8

The result is 8, one more than the formula above since cell B9 is now included in the count.

SUMPRODUCT function

Another way to solve this problem is with the SUMPRODUCT function and Boolean algebra:

=SUMPRODUCT(--(data>0))

Working from the inside out, this expression checks if values in data (B5:B15) are greater than zero:

data>0

Because data contains eleven cells, the result from this expression is an array that contains 11 TRUE and FALSE values:

{TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE}

To convert the TRUE and FALSE values to 1s and 0s, we use a double negative (--):

--{TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE}

The resulting array inside the SUMPRODUCT function looks like this:

=SUMPRODUCT({1;1;1;0;0;1;0;1;0;1;1}) // returns 7

With a single array to process, SUMPRODUCT sums the array and returns 7 as the result. To include zero, adjust the logical test like this:

=SUMPRODUCT(--(data>=0)) // returns 8

The result is 8, since cell B9 is now included in the count.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas