Can I use COUNTIF on two columns to compare values?

Question

I have two columns in Excel, like this:

 A   B
0.5  0.4
0.6  0.59
0.1  0.2
0.3  0.29

I want to count how many of the values in B are less than their corresponding values in A. In this case, the answer is 3.

I can do this by adding an extra column, B-A and then doing COUNTIF(RANGE, "<0"), but I am wondering if there's a way to do it without adding an extra column.

I realize this is on the fringes of what one might consider programming, but hopefully it's just on the right side of the line, rather than the wrong side.

Answer 1

Actually this is something I would do with a program.

Create a macro to:

• insert column C.
• set range("cN").value to "=bN-aN" for all N where range("aN").value <> "".
• do your countif calculation and shove it into a cell (not column C).
• delete column C.

There may be an easier non-programming way but I don't know it (and then your question would be closed anyhow).