Excel -- COUNTIF based on multiple cells

Question

Hi,

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 realise 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...

Ben

Answer 1

There is a solution, but it still involves 2 extra cells: DCOUNT.

The following is an example(insert into the specified cells the exact text after the colons):

A1:Condition

A2:=B4>A4

A3:A

B3:B

A4:700

B4:5000

A5:700

B5:600

A6:7000

B6:6000

A7:700

B7:701

Cell with count formula:=DCOUNT(A3:B7,"B",A1:A2)

Answer 2

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).

Answer 3

This can be done using Excel array formulas. Try doing something like this:

=SUM(IF(A1:A5 > B1:B5, 1, 0))

The very very important part, is to press CTRL-SHIFT-ENTER instead of just ENTER when you finished inputting the formula. Otherwise it won't understand you want to treat the data as an array.

Answer 4

scraimer solution is ok. But for a fun,,

You can also write a macro like the following and assign

=myOwnFunction(A1:A5,B1:B5)

this approach can be extended for any other logical function such as A * B + C < C + D * E etc....

Function myOwnFunction(R1 As Range, S1 As Range)

Dim J As Integer

Dim Size As Integer

Dim myCount As Integer

Size = R1.Cells.Count

myCount = 0

   For J = 1 To Size

       If (R1.Cells(J) > S1.Cells(J)) Then

        myCount = myCount + 1

       End If

   Next J


myOwnFunction = myCount

End Function