CODEWITHSUNDEEP
excelvbacountif
JakeyJake
JakeyJake

Reputation: 3

Referring to subsequent cells in CountIf

After an extensive and ineffectual google search, I'm hoping someone can help me out: I want to count the occurrences in a column of cells where a cell is greater than the cell that follows it, i.e.:

_A
1 2
2 1
3 2
4 1

Would return a countif of 2 as A1>A2, and A3>A4.

I also want to refer to offsets from the current cell being checked in the form of something like (with intentions in braces):

=countif(A:A, [Current cell being checked] ">" [Cell below the current cell being checked])

I can't seem to drum up these types of this. references. I know I could write a quick macro to do this but I'm interested in knowing this method of referencing.

Upvotes: 0

Views: 1042

Answers (1)

barry houdini
barry houdini

Reputation: 46371

You can't compare one range with another with COUNTIF

You could use SUMPRODUCT like this:

=SUMPRODUCT((A1:A3>A2:A4)+0)

or more generically

=SUMPRODUCT((Range>OFFSET(Range,1,0))+0)

Upvotes: 3

Related Questions