Count Number of Cells in Column B if Cells in Column A are less?

[razzandy]

Hi Guys

Driving me mad this

Lets say we have a list of numbers in Columns BD and Column BE as below and I want to count how many of the cells in Column BE are greater then the adjacent Cells in Column BD? I have tried Countif and Countifs (=COUNTIF(BE:BE,">"&BD2)) but it counts anything in the column which is above that particular cell i.e cell BE2 or BE3 and so on. I can do a simple =IF(BE2>BD2,1,"") in one Column to return 1's then =COUNT(BJ:BJ) to count the 1's but its just so long winded and I want to be able to use it in VBA with Application.WorksheetFunction.CountIf(). I was really hoping Countif would have worked but I think I have a mental block due to working on it for so long!

Thanks in advance and have a great weekend!

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

 

[razzandy]

Sorry, Office 2007. I hadn't realised they had added that. It looks like there's been a lot of changes since I was last on here. Been registered since 2002!

 

[Fluff]

There have indeed been a lot of changes.
Thanks for updating your profile.
Try

=SUMPRODUCT(--(BE2:BE100>BD2:BD100))

 

[razzandy]

That's Great and works fine but I am have never used that formula before and cant get my head around what its doing even after looking at the help files

What's the dashes for -- and are the inner brackets to signify an array?

I am just writing the VBA Version of this and will post it on here one I have it working.

Thanks again

 

[Fluff]

The -- is a double unary & it converts an array of True/False into 1 or 0 & the sumproduct then adds them up.

 

[razzandy]

Now I'm struggling with the VBA side of it I have wrote it as below but I get a Type Mismatch Error! Any Ideas? LstRw is declared earlier in the code and represents the last used row.

    Debug.Print Application.WorksheetFunction.SumProduct _
    (--(Sheets("Orders").Range("BE2:BE" & LstRw) > Sheets("Orders").Range("BD2:BD" & LstRw)))

 

[Fluff]

One way to get the count in VBA is

Sub razzandy()
   Dim Ary As Variant
   Dim RngBE As Range
   
   With Sheets("Orders")
      Set RngBE = .Range("BE2", .Range("BE" & Rows.Count).End(xlUp))
      Ary = Filter(.Evaluate("transpose(if(" & RngBE.Address & ">" & RngBE.Offset(, -1).Address & ",1,""x""))"), "x", False)
   End With
   MsgBox UBound(Ary) + 1
End Sub

 

[razzandy]

One way to get the count in VBA is

Sub razzandy()
   Dim Ary As Variant
   Dim RngBE As Range
  
   With Sheets("Orders")
      Set RngBE = .Range("BE2", .Range("BE" & Rows.Count).End(xlUp))
      Ary = Filter(.Evaluate("transpose(if(" & RngBE.Address & ">" & RngBE.Offset(, -1).Address & ",1,""x""))"), "x", False)
   End With
   MsgBox UBound(Ary) + 1
End Sub

That's great Fluff Thankyou! I changed it slightly as below but its all derived from you:

   With Sheets("Orders")
        Set RngBE = .Range("BE2:BE" & LstRw)
        Ary = Filter(.Evaluate("transpose(if(" & RngBE.Address & ">" & RngBE.Offset(, -1).Address & ",1,""x""))"), "x", False)
   End With
        Debug.Print UBound(Ary) + 1

I am still wondering how I could get the Application.WorksheetFunction.SumProduct working. I'm just inquisitive and it bugs me when I can't suss something! I could probably get the arrays in but not sure about the -- bit, where it converts into 0 and 1's. Maybe I need to post this as a new question what do you think?

 

[Fluff]

I rarely use Worksheetfunction in VBA, so not sure why code doesn't work.