0

I am trying to count how many cells in a particular column have a higher value than the cell below them. This is how far I've gotten, but it doesn't work (it always returns 0, even if there is at least one cell bigger than the one below it):

=COUNTIF(B4:B500,">"&OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),1,0))

Background (in case anyone is wondering): I am using this to check if the values in this column are properly sorted (i.e. starting with the lowest value and moving down to the highest). If the formula returns a value greater than 1, I know that the list is not fully sorted.

Christoph
  • 411
  • 5
  • 9
  • A valid answer has been found, but just out of curiosity: why did the (admittedly more complicated) formula in the question not work? – Christoph May 11 '16 at 17:25

3 Answers3

4

Use the SUMPRODUCT:

=SUMPRODUCT(1*(B4:B16>B5:B17))

![enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • This seems to be the easiest solution since it does not involve an array formula (which means I can use it as a condition in an IF formula). However, I cannot seem to get it to work for me. The array formula works fine (but requires an extra cell to compute it). – Christoph May 11 '16 at 17:04
  • so using your references `=SUMPRODUCT(1*(B4:B500 – Scott Craner May 11 '16 at 17:07
  • In an IF() statement, `=IF(SUMPRODUCT(1*(B4:B5000,"Exists","None")` – Scott Craner May 11 '16 at 17:13
  • OK, now I found the "bug": in order to match my question, it should be =SUMPRODUCT(1*(B4:B499>B5:B500)) (the important change is the ">"). – Christoph May 11 '16 at 17:20
2

you could also use an array formula.

{=SUM(N(A1:A13<A2:A14))}

which will check your logical condition for each cell in the array, then convert it to either 0 or 1 by using N() function and then sum it up. i prefer this approach because, unlike SUMPRODUCT(), you can use it with other functions than SUM.

please remember that after inserting an array formula into a cell, you must confirm it by pressing CTRL+SHIFT+ENTER.

screenshot

szaman
  • 2,159
  • 1
  • 14
  • 30
  • If I want to use an array formula to check if a list is sorted, it would be even simpler to use =AND(A1:A12<=A2:A13) (which returns a true/false value. I understand that this was not the actual question but I thought I'd mention it for anyone with a similar problem.) – Christoph May 11 '16 at 17:08
0
=COUNTIF(B5:B16,">"&B4) then copy

enter image description here

csanjose
  • 154
  • 10