Countif greater than previous cell

Question

I want to count the number of times in a column that a cell value is equal to zero and the cell in the row under is greater than zero.

Let's say that my column is the one bellow.

Row 1: 5
Row 2: 2
Row 3: 0
Row 4: 4
Row 5: 3
Row 6: 0
Row 7: 5
Row 8: 0
Row 9: 2

Then the result of the formula should be 3 because of the 3 pairs:

Row 3: 0
Row 4: 4

Row 6: 0
Row 7: 5

Row 8: 0
Row 9: 2

I tried using countif or sumif but it seems to me that you can only input fixed reference cell. Any idea how I could do it?

SeanC SeanC · Accepted Answer · 2014-05-19T16:33:12

Sumproduct will work here

First, we build up the range we need to compare (I'm going to assume you are using column A)

A1:A8<A2:A9

note the offset by one, so we miss the last value in the firstst array, and the first cell in the second.

Convert from TRUE/FALSE values to 0/1 to add up

--(A1:A8<A2:A9)

and wrap this with SUMPRODUCT to add the numbers up

=SUMPRODUCT(--(A1:A8<A2:A9))

Note that you can use SUM instead of SUMPRODUCT, but you will have to remember to use an array formula with CTRL+ALT+ENTER - The formula will then look like this: {=SUM(--(A1:A8<A2:A9))}

Countif greater than previous cell

2 Answers